Formula to tag transaction lines

[cmacnab]

Hi, I have a list of transactions that I need to code each line with a location code. In each transaction there is a minimum of 4 lines (expense, AP, and 2 sales taxes lines) however only the expense line is already tagged with the location code. Trying to figure out a formula to tag the other lines with the same location code. The transaction numbers are unique for each transaction so this is the item to point at in a formula I believe.

Example:
line 1: Trans #1 10.00 expense location 1
line 2: Trans #1 0.50 GST location blank
line 3: Trans #1 1.00 QST location blank
line 4: Trans #1 11.50 AP location blank
line 5: Trans #1 10.00 expense location 5
line 6: Trans #1 0.50 GST location blank
line 7: Trans #1 1.00 QST location blank
line 8: Trans #1 11.50 AP location blank

Thank you in advance for your help.

 

[cmacnab]

Hi, I have a list of transactions that I need to code each line with a location code. In each transaction there is a minimum of 4 lines (expense, AP, and 2 sales taxes lines) however only the expense line is already tagged with the location code. Trying to figure out a formula to tag the other lines with the same location code. The transaction numbers are unique for each transaction so this is the item to point at in a formula I believe.

Example:
line 1: Trans #1 10.00 expense location 1
line 2: Trans #1 0.50 GST location blank
line 3: Trans #1 1.00 QST location blank
line 4: Trans #1 11.50 AP location blank
line 5: Trans #1 10.00 expense location 5
line 6: Trans #1 0.50 GST location blank
line 7: Trans #1 1.00 QST location blank
line 8: Trans #1 11.50 AP location blank

Thank you in advance for your help.

Thank you, I've solved this using a helper formula and Vlookup.

 

[smozgur]

Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[cmacnab]

Thank you, I've solved this using a helper formula and Vlookup.

Hi, as requested, this is what I did to solve my problem. Not elegant but it worked.
1. I filtered the transactions that were tagged with a location code
2. I pasted these transactions on a seperate worksheet
3. I used a combination of an IF function & Vlookup to find the location code. Basically, if the location was blank, look-up the transaction code and apply the location code in a new cell.

If anyone would like further details on my solution, don't hesitate to ask. As I said, far from a perfect solution, I still had to manually validate the lines (over 36,000 lines) to make sure everything was captured correctly.