Excel 2010 Move data from multiple columns/rows to single row

0

2

So frustrating! I get data sent to me and it looks like this:

a  1
a     2  2
a           3  3
b  1
b     2  2
b           3  3
b                 4  4
b                       5  5
b                             6  6

and I need it to look like this:

a  1  2  2  3  3
b  1  2  2  3  3  4  4  5  5  6  6

I have about 30 columns that need to move to the top value in their group, then removing the duplicates (to which there are about 33 rows of duplicates, trying to get it down to about 8 rows). I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!!

Same illustration, but graphically:

enter image description here     →     enter image description here

frustrated529

Posted 2014-05-29T15:16:12.730

Reputation: 11

30 columns? Maybe I'm missing something, but this seems like a manual operation that'll take no more than 5-10 minutes. – Wutnaut – 2014-05-29T15:23:48.800

Your question is confusing. Your title says "Move data from multiple columns to a single row". But the example you provide (a1a22a33...->a12233...) looks like sorting a row (and possibly eliminating duplicates). Can you be more specific about exactly what you want to do? – mkasberg – 2014-05-29T15:25:50.513

Sorry, I just edited it to make it more visually correct. I just need to move all the values in the columns 'up' to the first value that starts the duplicates in the primary column. – frustrated529 – 2014-05-29T15:26:43.663

1Am I understanding you correctly; you are trying to remove the --- so you can remove duplicates and sort the data? – CharlieRB – 2014-05-29T16:04:10.767

Is the identifier (a or b in the examples) always a single character? Is there any consistency to the offsets between rows of data, for instance you've got a space after the b on the line with two 3s? – Jason Aller – 2014-05-29T16:07:02.710

Good question. I am not allowed to post images yet so the '----' are supposed to be blank columns. So, this might be confusing, each character is supposed to signify a different column. Letter "A and B" column A/row1, Number 1 = Column B/row1, first '2' = column C/row2, second '2' = Column D/row2, first '3' = Column E/row 3, second '3' = Column F/row 3, first '4' = Column G/row 4, etc to the last '6' = column L/row 6 – frustrated529 – 2014-05-29T16:11:16.287

Can you upload a picture to hostr.co or similar and link it? Struggling to understand the problem.

– Jonny Wright – 2014-05-29T16:16:36.390

Jason, the identifier is not a single character, they are names – frustrated529 – 2014-05-29T16:17:31.920

http://hostr.co/CBfMGH4BdNDF – frustrated529 – 2014-05-29T16:27:14.693

Do the names (represented by a and b in your example) have a fixed width? Do additional rows always only include new information after the length of the previous row, or could there be "a 1 - 3" followed by "a - 2" such that the output is supposed to be "a 1 2 3". Is the supplied data in order, like the example? – Jason Aller – 2014-05-29T16:27:14.940

No, they do not have a fixed width. – frustrated529 – 2014-05-29T16:29:15.340

Excellll,I will try the below answer. If it works, do you think I can record a MACRO to accomplish the steps? – frustrated529 – 2014-05-29T16:41:26.047

Answers

2

You can achieve this without VBA in a few seconds. Just follow these steps.

WARNING: This process will overwrite your original data. If you want to keep the original data, do not do this.

  1. Select your data, as far to the right as you need to select everything.

    enter image description here

  2. Press F5. Click Special... in the Go To dialog that pops up. Select Blanks and click OK. This will select all the blank cells in your data.

    enter image description here

  3. Without clicking anywhere, paste the following into the formula bar. Be sure to adjust the cell references to match your data.

    =IF($A2=$A1,C2,"")

    Press Ctrl+Enter. This will apply the formula to all the selected cells. The formula checks if the row below has the same value in column A. If so, it returns the value immediately below. If not, it returns nothing.

    enter image description here

  4. Now, select all your data again, copy it and Paste Special... > Values back into the same range. This will remove the formulas you just entered but will keep the values.

  5. Finally, with all your data selected, go to the Data ribbon and select Remove Duplicates. Remove duplicates based on column A (the column with values a and b).

    enter image description here

    This will keep the top row for each value in column A and remove the rest. You should be left with what you want.

    enter image description here

Excellll

Posted 2014-05-29T15:16:12.730

Reputation: 11 857

1I will try the below answer. If it works, do you think I can record a MACRO to accomplish the steps? – frustrated529 – 2014-05-29T16:41:57.150

A recorded macro won't be general enough for this to work in all cases you could run into. For example, the formula used depends on where the first blank cell is, which will likely be different for different data. You'll have to go into the VBA editor and figure out how to make it general. If your goal is to have a reusable VBA procedure, personally, I'd skip recording the macro and just write my own code that doesn't follow this exact procedure. – Excellll – 2014-05-29T16:49:00.883

The above worked so Thank you very very much. I would like to have a VBA reusable procedure, so maybe I will record a MACRO and reverse engineer it. – frustrated529 – 2014-05-29T17:04:41.900

Asked: 2014-05-29T15:16:12.730

Viewed: 16 111 times

Active: 2015-11-18T14:31:25.163