I get data from three different sources in a format similar to this:

1) 2) 3)

A 1 A 2 A 3

B 1 B 2 D 3

C 1 D 2 G 3

D 1 E 2

E 1 G 2

F 1

G 1Each data source provides two columns of data, one for the name of the item and one for the value of the item. Identical letters are identical text, and the numbers are all different.

How do I align it to look like this?:

A 1 A 2 A 3

B 1 B 2

C 1

D 1 D 2 D 3

E 1 E 2

F 1

G 1 G 2 G 3I need the aligned name column to move and the number next to it to move along with it.

Thanks for the help!

✔ Best Answer

Since you didn't provide column headings or row numbers, I had to use my own in order to set up a spreadsheet. Obviously you'll need to adjust the formulas to match your data layout. I copied the data from your post and pasted it into spreadsheet. Here is an excerpt so you can see the layout I am using. Your data is in Columns A & B.

In D4:D13 I entered 1 - 10 for the Item #'s. This is the location for the Output table. Below the data excerpt I have posted the formulas that should be used in E4:G4 and dragged down to E13:G13 to produce the output table.

A B C D 1 INPUT 2 Data Set 1 3 Item Quantity Needed 1 4 1 200 . . . 13 10 256 10 14 Data Set 2 15 Item Quantity Needed 16 1 400 . . . 23 10 1024 24 Data Set 3 15 Item Quantity Needed 16 1 260 . . . 30 9 3328Enter these formulas in the cells given and drag them down to Row 13 of the corresponding column:

E4: =B4

This pulls the Quantities from B4:B13 for Items 1 - 10.

F4: =IF(ISNA(VLOOKUP(D4,$A$16:$B$23,2,0)),"",VLOOKUP(D4,$A$16:$B$23,2,0))

This pulls the Quantities from B16:B23 for the matching Item #'s in Data Set 2.

G4: =IF(ISNA(VLOOKUP(D4,$A$26:$B$30,2,0)),"",VLOOKUP(D4,$A$26:$B$30,2,0))

This pulls the Quantities from B26:B30 for the matching Item #'s in Data Set 3.

Now, I assume that the length of your data sets will change. I suggest that you DAGS for Dynamic Named Ranges and see if using named ranges instead of hardcoded ranges for each data set would work for you. Since a Dynamic Named Range can grow and shrink based on the amount of data in the range, it might make updates a little bit easier.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Let's start with this question: Are you using Excel? There is no mention of an application in your post, so we can't be sure. Assuming you are using Excel, your example data is slightly confusing since A1, B2, G3, etc. are cell references in Excel. Perhaps it would be better if you used different examples for your data.

Finally, I am having trouble figuring out how your columns are aligned in your post.Please click on the following line and read the instructions on how to post data in this forum, then repost your data, with Column and Row headings so that we can understand the layout better. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Oops, sorry about that. I'm using Excel 2007.

INPUT

Data Set 1Item # Quantity Needed 1 200 2 325 3 155 4 500 5 95 6 2 7 68 8 15 9 256 10 256Data Set 2

Item # Quantity Needed 1 400 2 1300 4 2000 5 380 7 272 8 60 9 1024 10 1024Data Set 3

Item # Quantity Needed 1 2600 2 4225 4 6500 7 884 9 3328

FINAL DESIRED OUTPUTItem # 1 200 400 2600 2 325 1300 4225 3 155 4 500 2000 6500 5 95 380 6 2 7 68 272 884 8 15 60 9 256 1024 3328 10 256 1024Hopefully that's easier to read. Let me know if I should further clarify something. Thanks.

I do need one more piece of clarification. In Data Set 1, you have quantities for all 10 items.

In Data Set 2, you have a subset of Data Set 1.

In Date Set 3, you have a subset of Data Set 2.Is that going to be consistent?

In other words, is it possible that Data Set 1 might not contain all 10 items, but Data Set 2 (or 3) might?

Is it possible that Data Set 3 could contain a subset of items from Data Set 1 that are not in Data Set 2?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, data set 1 contains all the values for all the items we use for a couple months.

Data set 2 contains a few of the items from data set 1 since that is all we use in one month. Data set 3 only contains items we use for one week so it contains a subset of data set 2. It should be mostly consistent with very few, rare exceptions.

Since you didn't provide column headings or row numbers, I had to use my own in order to set up a spreadsheet. Obviously you'll need to adjust the formulas to match your data layout. I copied the data from your post and pasted it into spreadsheet. Here is an excerpt so you can see the layout I am using. Your data is in Columns A & B.

In D4:D13 I entered 1 - 10 for the Item #'s. This is the location for the Output table. Below the data excerpt I have posted the formulas that should be used in E4:G4 and dragged down to E13:G13 to produce the output table.

A B C D 1 INPUT 2 Data Set 1 3 Item Quantity Needed 1 4 1 200 . . . 13 10 256 10 14 Data Set 2 15 Item Quantity Needed 16 1 400 . . . 23 10 1024 24 Data Set 3 15 Item Quantity Needed 16 1 260 . . . 30 9 3328Enter these formulas in the cells given and drag them down to Row 13 of the corresponding column:

E4: =B4

This pulls the Quantities from B4:B13 for Items 1 - 10.

F4: =IF(ISNA(VLOOKUP(D4,$A$16:$B$23,2,0)),"",VLOOKUP(D4,$A$16:$B$23,2,0))

This pulls the Quantities from B16:B23 for the matching Item #'s in Data Set 2.

G4: =IF(ISNA(VLOOKUP(D4,$A$26:$B$30,2,0)),"",VLOOKUP(D4,$A$26:$B$30,2,0))

This pulls the Quantities from B26:B30 for the matching Item #'s in Data Set 3.

Now, I assume that the length of your data sets will change. I suggest that you DAGS for Dynamic Named Ranges and see if using named ranges instead of hardcoded ranges for each data set would work for you. Since a Dynamic Named Range can grow and shrink based on the amount of data in the range, it might make updates a little bit easier.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Wow, Thanks for all your help and patience with me! You rock! Edit: I tested it and it works like a charm. It's also easier for me to work with formulas rather than VBA code, so thanks for that also.

Just keep in mind that the "very few, rare exceptions" that you mention are probably going to cause problems. For example, Data Set 2 and 3 both contain Item # 4, so Columns E, F and G all show a quantity.

Now, change the 4 to a 6 in Data Set 3 to include an Item that is not contained in Data Set 2. What you will find is that the quantity for the 6 shows up in Column G, leaving a "gap" in Column F. There may be a way to nest VLOOKUP's to eliminate the gap, but I don't have the cycles to work on that at the moment.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I've taken that into consideration and I've been able to nest a VLOOKUP in there to check every data set in case a blank value is returned. I simply copy/paste the item names from each column to one column and use the remove duplicates & sort function to make sure all values are accounted for. After that, the nested VLOOKUP function takes care of it. Thanks for all your help in getting me this far!

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History