Grouping Multiple Excel Sheets via Speciifc Column ID

Good Morning, have a problem been attempting to solve with vlookup with no success. I have 3 excel spreadsheets, 2 are the input sheets and the third is for completed display. The unique id will be the custom ID number which is on all documents. now the columns are different between the two input sheets, and my goal is this. Using the ID, look at each sheet and import the data to the correct columns in the display sheet.. IE like this. Sheet 1, ID is column 1. Ranges 2 through 20 go to display sheet for use. They populate 2 through 20. Now sheet 2, using column 1 id, the ranges 2 through 20 columns are different then sheet 1 and I want them to go to end of string so say 21 through 41 cells. That is the ultimate goal. I am having problems achieving this.

Now the only other item I have to consider, sometimes sheet 1 will have custom id that are not on sheet 2, but I want them to still show up on display sheet. So that is why I am using vlookup.

Any assistance would be appreciated?

V/r

Hello and welcome to the forum.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Okay added attachments. Hope did this right. Thank you!

Oh you have 3 different workbooks, not worksheets.

You have a couple of different options here. Here's one:

Combined!B2 =INDEX('[Book 1.xlsx]Sheet1'!B:B,MATCH($A2,'[Book 1.xlsx]Sheet1'!$A:$A,0))&""
Drag through E2 then down.

Combined!F2 =INDEX('[Book 2.xlsx]Sheet1'!B:B,MATCH($A2,'[Book 2.xlsx]Sheet1'!$A:$A,0))&""
Drag through I2 then down.

Thank you, this is going to work I do believe for what I need. Testing it out now and will report findings. I do appreciate the quickness and thorough explanation!

Okay this is the two problems I have, I do have some that have multiple ID in same cell (so they don't show up properly) and I have to manually enter the ID (would like to have it auto populate so I don't miss anything) when dealing with 100's of lines. Working on what you gave 63falcondude. Thanks so far!

Happy to help.

If you upload a more representative sample of your data, I'll take a look.

I'm confident that we can come up with a solution that works for you.

Will do so in morning (left project at work) and now home but yes i will get you more information and use some screen shots to compliment my request.

V/r
Belszy

Morning, I have uploaded two screen shots with what I am trying to do. Basically the problem is this, there are two columns of ID that I am trying to match not just one (didn't realize it at first), but the ID can have multiple numbers as you can see on screen shots (they are grouped into one long string) which I guess can work if it matches, but I want to be able to pick out say a 51 out of there and match it to other document and fill in respective columns. The first equation worked very well for single occupant cells, but add in multiple and didn't factor in to well. Let me know what you think? If you have questions please ask!

V/r
Belszy

This has turned into a completely different request due to the layout of your actual data.

This is a terrible way to store data. I recommend cleaning your data so that you are then able to use it for analysis.

This means having one value per cell with the other values repeating down the column.

For example, using your 2nd screenshot, cell B19 would have 51, C19 would have 4500561605, D19 would have #005, etc.
and B20 would have 122, C20 would have 4500561605, D20 would have #005, etc.

That being said, there's not much that I can do with screenshots. Samples like you shared in post #3 (including the expected outcome) are much more helpful.

63falcondude , okay I have taken your advice and broken apart the sheet into single line items for the ids. I did this on both sheets, so now here is the way documents are layed out. Sheet 1, Column A is main item number, Column B is secondary. On the combined excel document with the formula, if first or second column are not present on combined sheet, I would like it to add them. Then when I do sheet 2, Column B is main search and Column A is secondary (meaning it needs to look at column B first and search combined Column A for a hit, if that matches, then needs to look at column A and match that with Column B of the combined for a match. If it matches fills in appropriate data, if no match found, then add to bottom of sheet.

Is this to complex for excel?

V/r
Belszy

It is difficult to work with screenshots.
I recommend uploading a small representative sample workbook of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Done per request!

SSID and SS LD are same numbers.. just one person puts it in column A, and another in Column B. ID and TM LD are same (all broken into single rows).

yes they should join first by looking at ID, if that matches then checks the SSID, if it matches, data joins, if it doesnt match throw next line down?