Trying to make excel combine column data across multiple sheets using a common identifier

[keith1970]

Hi all,

I've used excel for a while but only using basic functions.

Ive come to a stage where I need assistance. Im even struggling to work out how to explain it.

Each row has data relating to a certain specified ItemNumber (in header) and has the same ItemNumber is in every sheet so Im trying to collate all the data which is specific to that ItemNumber into one sheet.

I hope this makes sense and have added an example sheet as an attachment

EDIT: So as Carim has done in the example he sent, I wanted Excel to look up find an "ItemNumber" which is apparent in every sheet and put the data to the right of it all onto one sheet with the ItemNumber on the first column and the rest of the cells from the other sheets in the same row from the same ItemNumber.

[Glenn Kennedy]

If you can't explain it... show it. Please amend your sheet to show us EXACTLY what you want to see and EXACTLY where you want to see it.

[Carim]

Hi,

Is there something preventing you from using the combination Index / Match ...?

[keith1970]

Hi,

Is there something preventing you from using the combination Index / Match ...?

Maybe a lack of knowledge

[Carim]

Attached is your test file ...

The formula in cell B2 will probably help you out ...

[keith1970]

Attached is your test file ...

The formula in cell B2 will probably help you out ...

Thanks for the help Carim but I was hoping to get a result like in the attached examples first sheet (I copied and pasted the info in of course from the other sheets )
The ItemNumber could be on any row in the sheets but I need to get matched info. Its beyond my pay grade lol I am but a mere shopkeeper

[Carim]

Hi again,

The proposed formula just had to be extended ...

Attached is your test file with the two areas colored in orange and yellow to retrieve your data

[AliGW]

Attached is your test file ...

The formula in cell B2 will probably help you out ...

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[keith1970]

I'd also be grateful if you could show how you had done the formula so I could replicate it on the larger spreadsheet I have with is over 7k rows long.
I did try to copy and paste the formula but just got a big #REF error.

[JeteMc]

To pull the data from 7,000 rows change the range of the INDEX array from A1:J5 to A1:J7000.
You may also want to modify the formulas on the Content sheet as follows:
In columns I:P pulled from the Drag Price List sheet: =INDEX('Drag Price List'!$A$1:$J$7000,MATCH($A2,'Drag Price List'!$A$1:$A$7000,0),MATCH(I$1,'Drag Price List'!$A$1:$J$1,0))
In columns Q:W pulled from the PE Export New sheet: =INDEX('PE Export New'!$A$1:$J$7000,MATCH($A2,'PE Export New'!$A$1:$A$7000,0),MATCH(Q$1,'PE Export New'!$A$1:$J$1,0))
That said a #REF error may suggest that there are issues that go beyond changing the ranges. In order to trouble shoot we probably need to see a small sample of the workbook that is producing the error.
Let us know if you have any questions.