# 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

#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 1Item # Quantity Needed 1 200 2 325 3 155 4 500 5 95 6 2 7 68 8 15 9 256 10 256 Data Set 2Item # Quantity Needed 1 400 2 1300 4 2000 5 380 7 272 8 60 9 1024 10 1024 Data Set 3Item # 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 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

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 •