# Solved Aligning Columns of data

May 2, 2013 at 09:04:40
Specs: Windows XP
 I get data from three different sources in a format similar to this:1) 2) 3)A 1 A 2 A 3B 1 B 2 D 3C 1 D 2 G 3D 1 E 2E 1 G 2F 1G 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 3B 1 B 2C 1D 1 D 2 D 3E 1 E 2F 1G 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!

See More: Aligning Columns of data

May 3, 2013 at 06:33:21
 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 3328```Enter 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.

#1
May 2, 2013 at 12:00:14

Report •

#2
May 2, 2013 at 12:46:19
 Oops, sorry about that. I'm using Excel 2007.INPUTData Set 1```Item # Quantity Needed 1 200 2 325 3 155 4 500 5 95 6 2 7 68 8 15 9 256 10 256 ```Data Set 2```Item # Quantity Needed 1 400 2 1300 4 2000 5 380 7 272 8 60 9 1024 10 1024 ```Data Set 3```Item # Quantity Needed 1 2600 2 4225 4 6500 7 884 9 3328 ```FINAL DESIRED OUTPUT```Item # 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 1024 ```Hopefully that's easier to read. Let me know if I should further clarify something. Thanks.

Report •

#3
May 2, 2013 at 13:14:02
 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.

Report •

Related Solutions

#4
May 2, 2013 at 17:50:35
 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.

Report •

#5
May 3, 2013 at 06:33:21
 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 3328```Enter 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.

Report •

#6
May 3, 2013 at 06:37:37
 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.

Report •

#7
May 3, 2013 at 07:43:45
 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.

Report •

#8
May 3, 2013 at 08:44:26
 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!

Report •