Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a set of columns like this in excel:

ID                                      sizeAndColourAndSku_Value
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Select size
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Extra Small - Available (only 1 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Small - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Medium - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Large - Available (only 1 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size
4697ce05-2e50-4e67-85d5-ad733e15ae95    6 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    8 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    10 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    12 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    14 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    16 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size

I need to combine merge them by their ID so the results become like this:

2ff0d3d7-8c18-4386-83be-1b2485fb4ea9   Extra Small  Small  Medium  Large
4697ce05-2e50-4e67-85d5-ad733e15ae95   6      8      10     12      14

I read here that the quickest and easiest way to do this is with a pivot table in excel

http://www.excelbanter.com/showthread.php?t=235727

However I'm using excel 2010 and the method they've provided doesn't really work, anyone able to help me out with this?

Thanks

share|improve this question
1  
Asked as well on SO here. Do you have a maximum of sizes for each ID? –  Jerry Aug 8 '13 at 13:11
    
sorry, but what do you mean "maximum of sizes for each ID"? –  jc.yin Aug 8 '13 at 13:14
    
So far, there is "Extra Small", "Small", "Medium", "Large" for the first item, "6", "8", "10", "12", "14" and "16" for the second item. So, item 1 has 4 sizes, item 2 has 5 sizes. Is that the maximum sizes possible? Is there an item with, say, 100 different sizes? –  Jerry Aug 8 '13 at 13:16
    
umm not sure, the size column values can be pretty dynamic. It's not supplied by me, but rather by the client so I really have no idea how big it can go. But since it's for clothes I'm assuming not to 100 –  jc.yin Aug 8 '13 at 13:18
    
Hmm, I don't know easy ways, and there'll probably be a lot of formulas, some helpers and some for the final results. I assume that the data is currently in columns A and B. Try running =IF(B2=$B$2,0,C1+1) in column C as from row 2 (row 1 contains headers, right?) till the end. Put a filter and check the largest number. –  Jerry Aug 8 '13 at 13:32
show 5 more comments

1 Answer

I'm not 100% clear on what you're looking for, but I think you just want these rows aggregated by ID with the text to the left of the hyphen from each row entered in a new column to the right of the ID.

Two steps:

  1. Copy the ID column to where you want your new merged table. Apply Remove Duplicates (on the Data ribbon) to the copied column. This will give you your column of unique IDs.

  2. Select the first blank cell to the right of the first ID in your new table. Paste the following formula into the formula bar and press Ctrl+Shift+Enter to enter it as an array formula.

=IFERROR(LEFT(INDEX($B$1:$B$12,SMALL(IF($A$1:$A$12=$F1,ROW($B$1:$B$12)),COLUMN(A1))),FIND(" - ",INDEX($B$1:$B$12,SMALL(IF($A$1:$A$12=$F1,ROW($B$1:$B$12)),COLUMN(A1))))-1),"")

Here A1:A12 is the ID column in the original data, and B1:B12 are values. F1 is the location of the first ID in the new merged table. Adjust these ranges as necessary to match your data.

Finally fill over and down as far as you need to.

enter image description here

share|improve this answer
    
Okay I've completed the remove duplicates part, I've placed the new column of ID's at F1 to avoid confusion. I've applied the formula but I'm getting a blank cell afterwards –  jc.yin Aug 8 '13 at 15:51
    
@jc.yin Does your original data start in Row 1? If not, make the first argument of the INDEX functions start at row 1 (and extend to the last item in your data). This could fix the problem. –  Excellll Aug 8 '13 at 20:12
    
Yea it does, now it just gives in columnG1 the exact same value as columnB1, here's a link to my test file link –  jc.yin Aug 9 '13 at 1:08
    
You didn't successfully enter the formula as an array formula. Double click G1 and then press Ctrl+Shift+Enter. You'll see the formula will be wrapped in curly brackets if you do this successfully, e.g. {=IFERROR(...)}. Once you've done that, fill over and down. Note, you have to do array entry like this anytime you edit the formula. Also note, The first column will be blank because of the "Select Size" entries with no hyphens. This column can be removed afterward if you want. –  Excellll Aug 9 '13 at 2:01
    
I did Ctrl+Shift+Enter and the formula was surrounded with curly brackets. However the cell remains empty. When I click the formula bar, the curly brackets disappear and then the cell has a value again, but only one. –  jc.yin Aug 9 '13 at 3:10
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.