Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > adding a "relationship code" to a worksheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 48
Default adding a "relationship code" to a worksheet
Hi. what I mean by "relationship code" is this. a relationship between
transactions. Here is a sample of what I have and need.

Have

PO Item Type Material Number MvT Pstg date
12345 10 NB 789 101 1/6/2009
12345 10 NB 789 321 1/8/2009
12345 20 NB ABC 101 1/7/2009
12345 20 NB ABC 101 1/7/2009
12345 20 NB ABC 321 1/8/2009
12345 20 NB ABC 321 1/8/2009
56789 60 NB A1A 101 2/4/2009
56789 60 NB A1A 321 2/5/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009


Need

PO Item Type Material Number MvT Pstg date Code
12345 10 NB 789 101 1/6/2009 1000
12345 10 NB 789 321 1/8/2009 1000
12345 20 NB ABC 101 1/7/2009 1000
12345 20 NB ABC 101 1/7/2009 1001
12345 20 NB ABC 321 1/8/2009 1000
12345 20 NB ABC 321 1/8/2009 1001
56789 60 NB A1A 101 2/4/2009 1000
56789 60 NB A1A 321 2/5/2009 1000
56789 90 NB AAA 101 1/13/2009 1000
56789 90 NB AAA 101 1/13/2009 1001
56789 90 NB AAA 101 1/13/2009 1002
56789 90 NB AAA 101 1/13/2009 1003
56789 90 NB AAA 101 1/13/2009 1004
56789 90 NB AAA 101 1/13/2009 1005
56789 90 NB AAA 101 1/13/2009 1006
56789 90 NB AAA 321 1/16/2009 1000
56789 90 NB AAA 321 1/16/2009 1001
56789 90 NB AAA 321 1/16/2009 1002
56789 90 NB AAA 321 1/16/2009 1003
56789 90 NB AAA 321 1/16/2009 1004
56789 90 NB AAA 321 1/16/2009 1005
56789 90 NB AAA 321 1/16/2009 1006

The code can be anything. I just used 1000. This way, I can export to
Access and then get the information I need. Can someone assist me?

Thanks

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 7,247
Default adding a "relationship code" to a worksheet

You need to describe clear how two or more transactions are related?
What piece of information ties one transaction to another?

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Fri, 15 Jan 2010 08:13:16 -0800 (PST), pat67
wrote:

Hi. what I mean by "relationship code" is this. a relationship between
transactions. Here is a sample of what I have and need.

Have

PO Item Type Material Number MvT Pstg date
12345 10 NB 789 101 1/6/2009
12345 10 NB 789 321 1/8/2009
12345 20 NB ABC 101 1/7/2009
12345 20 NB ABC 101 1/7/2009
12345 20 NB ABC 321 1/8/2009
12345 20 NB ABC 321 1/8/2009
56789 60 NB A1A 101 2/4/2009
56789 60 NB A1A 321 2/5/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 101 1/13/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009
56789 90 NB AAA 321 1/16/2009


Need

PO Item Type Material Number MvT Pstg date Code
12345 10 NB 789 101 1/6/2009 1000
12345 10 NB 789 321 1/8/2009 1000
12345 20 NB ABC 101 1/7/2009 1000
12345 20 NB ABC 101 1/7/2009 1001
12345 20 NB ABC 321 1/8/2009 1000
12345 20 NB ABC 321 1/8/2009 1001
56789 60 NB A1A 101 2/4/2009 1000
56789 60 NB A1A 321 2/5/2009 1000
56789 90 NB AAA 101 1/13/2009 1000
56789 90 NB AAA 101 1/13/2009 1001
56789 90 NB AAA 101 1/13/2009 1002
56789 90 NB AAA 101 1/13/2009 1003
56789 90 NB AAA 101 1/13/2009 1004
56789 90 NB AAA 101 1/13/2009 1005
56789 90 NB AAA 101 1/13/2009 1006
56789 90 NB AAA 321 1/16/2009 1000
56789 90 NB AAA 321 1/16/2009 1001
56789 90 NB AAA 321 1/16/2009 1002
56789 90 NB AAA 321 1/16/2009 1003
56789 90 NB AAA 321 1/16/2009 1004
56789 90 NB AAA 321 1/16/2009 1005
56789 90 NB AAA 321 1/16/2009 1006

The code can be anything. I just used 1000. This way, I can export to
Access and then get the information I need. Can someone assist me?

Thanks
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 48
Default adding a "relationship code" to a worksheet
On Jan 15, 12:19*pm, Chip Pearson wrote:
You need to describe clear how two or more transactions are related?
What piece of information ties one transaction to another?

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

On Fri, 15 Jan 2010 08:13:16 -0800 (PST), pat67
wrote:



Hi. what I mean by "relationship code" is this. a relationship between
transactions. Here is a sample of what I have and need.

Have

PO *Item * *Type * *Material Number MvT * * Pstg date
12345 * * * 10 * * *NB * * *789 * * * * * * * * * * 101 * * 1/6/2009
12345 * * * 10 * * *NB * * *789 * * * * * * * * * * *321 * *1/8/2009
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * *101 * *1/7/2009
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * *101 * *1/7/2009
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * * 321 * 1/8/2009
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * * 321 * 1/8/2009
56789 * * * 60 * * *NB * * *A1A * * * * * * * * * * * 101 * 2/4/2009
56789 * * * 60 * * *NB * * *A1A * * * * * * * * * * *321 * *2/5/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009

Need

PO *Item * *Type * *Material Number MvT * * Pstg date * * * *Code
12345 * * * 10 * * *NB * * *789 * * * * * * * * * * 101 * * 1/6/2009 * * * * * 1000
12345 * * * 10 * * *NB * * *789 * * * * * * * * * * *321 * *1/8/2009 * * * * * 1000
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * *101 * *1/7/2009 * * * * * *1000
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * *101 * *1/7/2009 * * * * * *1001
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * * 321 * 1/8/2009 * * * * * *1000
12345 * * * 20 * * *NB * * *ABC * * * * * * * * * * * 321 * 1/8/2009 * * * * * *1001
56789 * * * 60 * * *NB * * *A1A * * * * * * * * * * * 101 * 2/4/2009 * * * * * *1000
56789 * * * 60 * * *NB * * *A1A * * * * * * * * * * *321 * *2/5/2009 * * * * * *1000
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1000
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1001
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1002
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1003
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1004
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1005
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *101 * *1/13/2009 * * * * *1006
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1000
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1001
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1002
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1003
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1004
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1005
56789 * * * 90 * * *NB * * *AAA * * * * * * * * * * *321 * *1/16/2009 * * * * *1006

The code can be anything. I just used 1000. This way, I can export to
Access and then get the information I need. Can someone assist me?

Thanks- Hide quoted text -

- Show quoted text -

notheng ties them together. that's the issue. I want to tie them
together based on their move tyoe and date. FIFO. The first 101 date
goes with the first 321 date
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,180
Default adding a "relationship code" to a worksheet
Excel 2007 Table
Assign count of repetitions of a unique row,
to the row,
starting from the first row,
and ending at the current row.
Table needs not to be sorted.
http://c0444202.cdn.cloudfiles.racks.../01_16_10.xlsx

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 48
Default adding a "relationship code" to a worksheet
On Jan 16, 10:04*am, Herbert Seidenberg
wrote:
Excel 2007 Table
Assign count of repetitions of a unique row,
to the row,
starting from the first row,
and ending at the current row.
Table needs not to be sorted.http://c0444202.cdn.cloudfiles.racks.../01_16_10.xlsx

Thanks. That works perfectly
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding data only if adjacent to cell labeled "male"/"female" lovesrubbaducky Excel Worksheet Functions 1 November 23rd 09 05:49 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding chubbybat Excel Discussion (Misc queries) 9 March 5th 08 01:40 PM
Adding "and" to Spellnumber code Ken G. Excel Discussion (Misc queries) 10 July 22nd 06 12:53 PM
Adding a "CLEAR" button to the worksheet Billy Excel Discussion (Misc queries) 1 January 8th 06 06:09 PM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement