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:

Acct	Code	Value
12345C	ABC	500.00
12345C	ABC	125.00
12345C	ABC	956.00
12345C	XYZ	-456.00
12345C	NMO	512.00
12345U	NMO	362.00
12345U	XYZ	1432.00
12345U	XYZ	856.00
98765U	XYZ	-1256.00
98765U	ABC	2563.00
98765U	ABC	996.00
98765U	ABC	-50.00
98765C	XYZ	785.00
98765C	NMO	392.00
98765C	NMO	441.00

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

Sample Destination

Acct	Code	Value
12345C	ABC	1581.00
12345C	XYZ	-456.00
12345C	NMO	512.00
98765C	XYZ	785.00
98765C	NMO	833.00
Sum CAD	3255.00
12345U	NMO	362.00
12345U	XYZ	2288.00
98765U	XYZ	-1256.00
98765U	ABC	3509.00
Sum USD		4903.00

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

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

Type	C				Type	U
Idx	Acct	Code	Value		Idx	Acct	Code	Value
1	12345C	ABC	500		6	12345U	NMO	362
2	12345C	ABC	125		7	12345U	XYZ	1432
3	12345C	ABC	956		8	12345U	XYZ	856
4	12345C	XYZ	-456		9	98765U	XYZ	-1256
5	12345C	NMO	512		10	98765U	ABC	2563
13	98765C	XYZ	785		11	98765U	ABC	996
14	98765C	NMO	392		12	98765U	ABC	-50
15	98765C	NMO	441

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

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