ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   adding a "relationship code" to a worksheet (https://www.excelbanter.com/excel-worksheet-functions/253553-adding-relationship-code-worksheet.html)

pat67
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

Chip Pearson
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

pat67
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

Herbert Seidenberg
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

pat67
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


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com