EMERGENCY!!!! Complex V-lookup HELP!!!

moonlight22

New Member
Joined
Aug 15, 2014
Messages
24
Hello,

I have a file which has multiple sheets. The first is my data sheet and the rest are destinations sheets.
The data is copied from another system and the destination sheets are required to pull very specific information with multiple factors to consider from the data sheet.

Basically this is what I need to accomplish:

In the data sheet there are reference codes, and account numbers. I currently have the complete list of account numbers which are split in my destination sheets by CAN and USD. They come mixed up in the extracted data which I paste into sheet1.
I do not have a complete list of the reference codes as they vary from month to month and can be 1 of thousands. Each code can correspond to any of the account numbers, and can vary monthly.

That said, I need for my destination sheet to find every occurrence of the code and the corresponding account number from sheet one. The tricky part being, if the code and account number appear more than once in sheet1 (which they can) I need the destination sheet to only pull it one. They also need to split into CAN or USD, which is identified by the last character of the account number (ie. 12345C or 12345U).

The destination sheet must have each code in column D, the corresponding account number in column A, and split by CAN and USD, as I have other formulas which pull other information based on these and sum certain numerical values by CAN and USD.

I am not sure if vlookup or hlookup is the way to go but that is all i could think of...

any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

moonlight22

New Member
Joined
Aug 15, 2014
Messages
24
Hi,

here are samples...

Sample Data set:
AcctCode Value
12345CABC500.00
12345CABC125.00
12345CABC956.00
12345CXYZ-456.00
12345CNMO512.00
12345UNMO362.00
12345UXYZ1432.00
12345UXYZ856.00
98765UXYZ-1256.00
98765UABC2563.00
98765UABC996.00
98765UABC-50.00
98765CXYZ785.00
98765CNMO392.00
98765CNMO441.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Sample Destination
AcctCodeValue
12345CABC1581.00
12345CXYZ-456.00
12345CNMO512.00
98765CXYZ785.00
98765CNMO833.00
Sum CAD3255.00
12345UNMO362.00
12345UXYZ2288.00
98765UXYZ-1256.00
98765UABC3509.00
Sum USD4903.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>


thanks!!
 

moonlight22

New Member
Joined
Aug 15, 2014
Messages
24

ADVERTISEMENT

Any suggestions where I can get a quick 'how-to'... never created a pivot table before...

Just a quick note...I dont need any help with the summing of cost, as I have a separate formula for that. only the Acct and Code is what I need help with. But if the pivot table can accomplish it all then thats great as well!
 

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
Pivot table would accomplish it all. Here's a link:
PivotTable reports 101 - Excel

Try the following:
1. Make sure your data has column headers
2. Highlight your data
3. Go to the Insert tab, select insert pivot table, click ok
4. In the Field list, Add Acct and Code as "row labels" by dragging the fields to the appropriate box.
5. In the Field List,AddValue as a value field by dragging them to the appropriate box.

When you have a pivot table selected(or are clicked in one) an additional area will appear in your ribbon called "Pivot Table Tools." In the design tab of said section you can edit /modify the layout of your report. For your purposes you can look into a tabular layout with repeating row items. But you can play around with this section to get it to where you want.

You can add the CAN/USD as a column in your source data, or if the data is already seperated (in seperate ranges) you can just create a second pivot table.

Regardless of what solution may find you. I HIGHLY suggest checking out pivot tables. They are incredibly Powerful and are useful for many different tasks.
 

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
Any suggestions where I can get a quick 'how-to'... never created a pivot table before...

Just a quick note...I dont need any help with the summing of cost, as I have a separate formula for that. only the Acct and Code is what I need help with. But if the pivot table can accomplish it all then thats great as well!

If it boils down to it and you already have a sum if formula, couldn't you just use the remove duplicates in the data tab?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Sheet1, A1:B20, houses the source sample, the headers included.

Sheet2

TypeC TypeU
IdxAcctCodeValue IdxAcctCodeValue
112345CABC500 612345UNMO362
212345CABC125 712345UXYZ1432
312345CABC956 812345UXYZ856
412345CXYZ-456 998765UXYZ-1256
512345CNMO512 1098765UABC2563
1398765CXYZ785 1198765UABC996
1498765CNMO392 1298765UABC-50
1598765CNMO441

<COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width=54><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 881" width=25><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2048" width=58><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

A3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(SMALL(IF(RIGHT(Sheet1!$A$2:$A$16)=$G$1,
  ROW(Sheet1!$A$2:$A$16)-ROW(Sheet1!$A$2)+1),ROWS($F$3:F3)),"")

G3, just enter, coy across, and down:
Rich (BB code):
=IF($F3="","",INDEX(Sheet1!A$2:A$16,$F3))

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(SMALL(IF(RIGHT(Sheet1!$A$2:$A$16)=$B$1,
  ROW(Sheet1!$A$2:$A$16)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3)),"")

B3, just enter, coy across, and down:
Rich (BB code):
=IF($A3="","",INDEX(Sheet1!A$2:A$16,$A3))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top