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

[moonlight22]

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.

 

[Aladin Akyurek]

Try to post a small source sample and a corresponding destination output.

 

[moonlight22]

Hi,

here are samples...

Sample Data set:
[TABLE="width: 184"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Acct[/TD]
[TD]Code[/TD]
[TD] Value[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]ABC[/TD]
[TD="align: right"]500.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]ABC[/TD]
[TD="align: right"]125.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]ABC[/TD]
[TD="align: right"]956.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]XYZ[/TD]
[TD="align: right"]-456.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]NMO[/TD]
[TD="align: right"]512.00[/TD]
[/TR]
[TR]
[TD]12345U[/TD]
[TD]NMO[/TD]
[TD="align: right"]362.00[/TD]
[/TR]
[TR]
[TD]12345U[/TD]
[TD]XYZ[/TD]
[TD="align: right"]1432.00[/TD]
[/TR]
[TR]
[TD]12345U[/TD]
[TD]XYZ[/TD]
[TD="align: right"]856.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]XYZ[/TD]
[TD="align: right"]-1256.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]ABC[/TD]
[TD="align: right"]2563.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]ABC[/TD]
[TD="align: right"]996.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]ABC[/TD]
[TD="align: right"]-50.00[/TD]
[/TR]
[TR]
[TD]98765C[/TD]
[TD]XYZ[/TD]
[TD="align: right"]785.00[/TD]
[/TR]
[TR]
[TD]98765C[/TD]
[TD]NMO[/TD]
[TD="align: right"]392.00[/TD]
[/TR]
[TR]
[TD]98765C[/TD]
[TD]NMO[/TD]
[TD="align: right"]441.00[/TD]
[/TR]
</tbody>[/TABLE]


Sample Destination
[TABLE="width: 184"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Acct[/TD]
[TD]Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]ABC[/TD]
[TD="align: right"]1581.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]XYZ[/TD]
[TD="align: right"]-456.00[/TD]
[/TR]
[TR]
[TD]12345C[/TD]
[TD]NMO[/TD]
[TD="align: right"]512.00[/TD]
[/TR]
[TR]
[TD]98765C[/TD]
[TD]XYZ[/TD]
[TD="align: right"]785.00[/TD]
[/TR]
[TR]
[TD]98765C[/TD]
[TD]NMO[/TD]
[TD="align: right"]833.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sum CAD[/TD]
[TD="align: right"]3255.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345U[/TD]
[TD]NMO[/TD]
[TD="align: right"]362.00[/TD]
[/TR]
[TR]
[TD]12345U[/TD]
[TD]XYZ[/TD]
[TD="align: right"]2288.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]XYZ[/TD]
[TD="align: right"]-1256.00[/TD]
[/TR]
[TR]
[TD]98765U[/TD]
[TD]ABC[/TD]
[TD="align: right"]3509.00[/TD]
[/TR]
[TR]
[TD]Sum USD[/TD]
[TD][/TD]
[TD="align: right"]4903.00[/TD]
[/TR]
</tbody>[/TABLE]


thanks!!

 

[ghrain22]

This looks like a job for a pivot table!

 

[moonlight22]

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]

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]

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]

Sheet1, A1:B20, houses the source sample, the headers included.

Sheet2

[TABLE="width: 390"]
<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>[TR]
[TD="class: xl65, width: 54, bgcolor: white"]Type[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 25, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 58, bgcolor: white"]Type[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]U[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 54, bgcolor: white"]Idx[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Acct[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Code[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Value[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 58, bgcolor: white"]Idx[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Acct[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Code[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Value[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]12345C[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]500[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl67, bgcolor: transparent"]12345U[/TD]
[TD="class: xl67, bgcolor: transparent"]NMO[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]362[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"]12345C[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]125[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl67, bgcolor: transparent"]12345U[/TD]
[TD="class: xl67, bgcolor: transparent"]XYZ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1432[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent"]12345C[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]956[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl67, bgcolor: transparent"]12345U[/TD]
[TD="class: xl67, bgcolor: transparent"]XYZ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]856[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl67, bgcolor: transparent"]12345C[/TD]
[TD="class: xl67, bgcolor: transparent"]XYZ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-456[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl67, bgcolor: transparent"]98765U[/TD]
[TD="class: xl67, bgcolor: transparent"]XYZ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-1256[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl67, bgcolor: transparent"]12345C[/TD]
[TD="class: xl67, bgcolor: transparent"]NMO[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]512[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent"]98765U[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2563[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl67, bgcolor: transparent"]98765C[/TD]
[TD="class: xl67, bgcolor: transparent"]XYZ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]785[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl67, bgcolor: transparent"]98765U[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]996[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl67, bgcolor: transparent"]98765C[/TD]
[TD="class: xl67, bgcolor: transparent"]NMO[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]392[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl67, bgcolor: transparent"]98765U[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-50[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl67, bgcolor: transparent"]98765C[/TD]
[TD="class: xl67, bgcolor: transparent"]NMO[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]441[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

A3, control+shift+enter, not just enter, and copy down:

=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:

=IF($F3="","",INDEX(Sheet1!A$2:A$16,$F3))

F3, control+shift+enter, not just enter, and copy down:

=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:

=IF($A3="","",INDEX(Sheet1!A$2:A$16,$A3))