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 3
B 1 B 2 D 3
C 1 D 2 G 3
D 1 E 2
E 1 G 2
F 1
G 1

Each 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 3

I 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

Report •


✔ Best Answer
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
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.


Report •

#2
May 2, 2013 at 12:46:19
Oops, sorry about that. I'm using Excel 2007.

INPUT
Data 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
✔ 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                         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 •


Ask Question