Cosolidating

Mike,

I suppose there are other means to consolidate. What follows is entirely formula-based.

Lets name that huge parts list SDATA (from source data) that spans columns A to E, the column A range with part numbers PARTS, and the column E range with values VALUES via the Name Box.

On the consolidation sheet,

in B2 enter: =IF(LEN(A2)>0,VLOOKUP(A2,SDATA,3,0),"")
in C2 enter: =IF(LEN(B2)>0,VLOOKUP(A2,SDATA,4,0),"")
in D2 enter: =IF(LEN(A2)>0,SUMIF(PARTS,A2,VALUES),"")

Select B2:D2 and copy down as far as needed.

Note. I took the part numbers to be unique; for that reason I used in D2 a SUMIF formula that does not test for country and classification.
If it is somehow necessary to test for them in order to add the values in E, I'd suggest a different formula for D2 on the consolidation sheet. Is such a test needed?

Aladin

=========

Your solutions

Mike:

The first solution can be an INDEX MATCH formula in columns C, D, and E on your other sheet (assuming your formulas start in row 2 of Sheet1, AND assuming all Part Numbers in Column A on Sheet 1 are unique entries, no duplicates):

Column C formula:
=IF(A2="","",INDEX(Sheet1!$A$2:Sheet1!$E$18569,MATCH(A2,Sheet1!$A$2:Sheet1!$A$18569,0),3))

Column D formula:
=IF(A2="","",INDEX(Sheet1!$A$2:Sheet1!$E$18569,MATCH(A2,Sheet1!$A$2:Sheet1!$A$18569,0),4))

Column E formula:
=IF(A2="","",INDEX(Sheet1!$A$2:Sheet1!$E$18569,MATCH(A2,Sheet1!$A$2:Sheet1!$A$18569,0),5))

Regarding your second problem, I'm not sure what you mean...how can a part number have the same value that represents a country and a classification? In any case, think about using the CONCATENATE function: =CONCATENATE(D100&" "&E100).

Any help?

Tom

Re: Your solutions

It doesn't seem to be working or maybe it is just not the way I need it to.
First I really sppriciate the help but I'm new to excel and am learning at work pretty much self taught so yo'll have to bear with me.

Let me clearify what I'm looking for.
In column A I have the part numbers starting from A4 to A18569

In Column C I have the the country of origin of the part number in column A all the way from C4 to C18569.

In column D I have the classification for te part number in column a from D4 to D18569,

In Column E I have the value of the part number in column A from E4 to E18569.

Example: part number 20122450140 is in A13. It's c/o is in C13, classification in D13, and value in E13.
What I want to do is be able to put a part number into a cell like I4. In J4 the c/o will appear, in K4 the classification will appear, and M4 the value will appear.

Any more help will be really appriciated.
Skip the classification for now. should learn one thing at a time :D

Re: Possible solutions follow-up

Mike --

You are welcome for the help, so here goes:

I apparently mistakenly interpreted your first post to mean that you wanted to have a separate sheet to enter a part number and have the corresponding related info pop up. It looks like you want to keep it all on the same sheet, which is no problem.

In cell J4, try entering this formula:
=IF(I4="","",INDEX($A$4:$E$18569,MATCH(I4,$A$4:$A$18569,0),3))

In cell K4, enter:
=IF(I4="","",INDEX($A$4:$E$18569,MATCH(I4,$A$4:$A$18569,0),4))

In cell L4 (your post said M4 but I think you meant L4 unless you really want to keep column L blank), enter:
=IF(I4="","",INDEX($A$4:$E$18569,MATCH(I4,$A$4:$A$18569,0),5))

Copy the formulas down as needed.

Now if you enter a part number in cell I4 that is among the part numbers listed in A4:A18569, the corresponding info should appear in cells J4, K4, and L4.

Hope this helps!

Tom

Thank-you

That is exacty what I was looking for. works great.