Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with similar lookup values
I have a table of data downloaded from a fixed asset ledger.
I need to find the P&L code for each PO. This is easy enough to do with VLOOKUP. However, here's the catch. Some assets don't have associated POs, and so "NO PO NUMBER" is in their place. The problem is this value "NO PO NUMBER" can be related to multiple P&L codes. Is there a way to associate each instance of "NO PO NUMBER" with its corresponding P&L code? Assume PO number is in A:A and P&L code is in B:B. (Yes, using Access is an option here. But I would prefer to do this analysis in Excel if possible.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with similar lookup values
You could apply a filter to column A to select NO PO NUMBER. Then in
the first cell visible you could change this to: ="NO PO NUMBER"& then click on the cell in column B on the same row. You might get something like: ="NO PO NUMBER"&B83 if 83 is the first visible row. With the filter still active you can copy this formula down the visible cells to give you the P&L code tagged on to the comment. You can then remove the filter. Is this the kind of thing you meant? Hope this helps. Pete On Jul 5, 3:47 pm, Dave F wrote: I have a table of data downloaded from a fixed asset ledger. I need to find the P&L code for each PO. This is easy enough to do with VLOOKUP. However, here's the catch. Some assets don't have associated POs, and so "NO PO NUMBER" is in their place. The problem is this value "NO PO NUMBER" can be related to multiple P&L codes. Is there a way to associate each instance of "NO PO NUMBER" with its corresponding P&L code? Assume PO number is in A:A and P&L code is in B:B. (Yes, using Access is an option here. But I would prefer to do this analysis in Excel if possible.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with similar lookup values
Thanks. I ended up solving this problem by doing something somewhat
similar. I created two helper columns, the first being: =IF(AG2="No PO Number",1,"") and filled down and the second using: =IF(AG2="No PO number",CONCATENATE(AG2,SUM(AN$1:AN2)),AG2) and filled down. This has the effect of creating NO PO NUMBER1, NO PO NUMBER2, etc. Then I just copied and pasted the values in the second helper column into the PO column, and so each row had a unique PO value from which VLOOKUP could be run. Circuitous, but it solves the problem. On Jul 5, 11:21 am, Pete_UK wrote: You could apply a filter to column A to select NO PO NUMBER. Then in the first cell visible you could change this to: ="NO PO NUMBER"& then click on the cell in column B on the same row. You might get something like: ="NO PO NUMBER"&B83 if 83 is the first visible row. With the filter still active you can copy this formula down the visible cells to give you the P&L code tagged on to the comment. You can then remove the filter. Is this the kind of thing you meant? Hope this helps. Pete On Jul 5, 3:47 pm, Dave F wrote: I have a table of data downloaded from a fixed asset ledger. I need to find the P&L code for each PO. This is easy enough to do with VLOOKUP. However, here's the catch. Some assets don't have associated POs, and so "NO PO NUMBER" is in their place. The problem is this value "NO PO NUMBER" can be related to multiple P&L codes. Is there a way to associate each instance of "NO PO NUMBER" with its corresponding P&L code? Assume PO number is in A:A and P&L code is in B:B. (Yes, using Access is an option here. But I would prefer to do this analysis in Excel if possible.)- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with similar lookup values
Ah well, I was on the right track, then. Glad to hear you got
something working, and thanks for feeding back. Pete On Jul 5, 5:45 pm, Dave F wrote: Thanks. I ended up solving this problem by doing something somewhat similar. I created two helper columns, the first being: =IF(AG2="No PO Number",1,"") and filled down and the second using: =IF(AG2="No PO number",CONCATENATE(AG2,SUM(AN$1:AN2)),AG2) and filled down. This has the effect of creating NO PO NUMBER1, NO PO NUMBER2, etc. Then I just copied and pasted the values in the second helper column into the PO column, and so each row had a unique PO value from which VLOOKUP could be run. Circuitous, but it solves the problem. On Jul 5, 11:21 am, Pete_UK wrote: You could apply a filter to column A to select NO PO NUMBER. Then in the first cell visible you could change this to: ="NO PO NUMBER"& then click on the cell in column B on the same row. You might get something like: ="NO PO NUMBER"&B83 if 83 is the first visible row. With the filter still active you can copy this formula down the visible cells to give you the P&L code tagged on to the comment. You can then remove the filter. Is this the kind of thing you meant? Hope this helps. Pete On Jul 5, 3:47 pm, Dave F wrote: I have a table of data downloaded from a fixed asset ledger. I need to find the P&L code for each PO. This is easy enough to do with VLOOKUP. However, here's the catch. Some assets don't have associated POs, and so "NO PO NUMBER" is in their place. The problem is this value "NO PO NUMBER" can be related to multiple P&L codes. Is there a way to associate each instance of "NO PO NUMBER" with its corresponding P&L code? Assume PO number is in A:A and P&L code is in B:B. (Yes, using Access is an option here. But I would prefer to do this analysis in Excel if possible.)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup(?) with 2 Lookup Values | Excel Worksheet Functions | |||
How do I use vlookup with two lookup values? | Excel Worksheet Functions | |||
Vlookup on a worksheet with similar values | Excel Worksheet Functions | |||
vlookup using two lookup values? | Excel Worksheet Functions | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions |