Combine multiple rows into one based on column?

Technowolf

Board Regular
Joined
Aug 28, 2014
Messages
181
I've got multiple rows (with no overlapping data) and duplicates in a column, that looks like this:

AASD
AREW
AER
AEWQ
XXX
BFG
BSFDG
BADSF
XXX
CASD
CDFGA
CDF

<tbody>
</tbody>


I need to compress those down to single rows each based on the column with the duplicates so it looks like:

AASDREWEREWQ
BFGSFDGADSF
CASDDFGADF

<tbody>
</tbody>

Ideas?

Edit: Looking for a VBA solution btw.
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Technowolf,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Are the XXX's actually in column A, or, do they represent a blank row?
 
Upvote 0
Technowolf,

Sample raw data:


Excel 2007
ABCDEFGHIJKL
1AASD
2AREW
3AER
4AEWQ
5XXX
6BFG
7BSFDG
8BADSF
9XXX
10CASD
11CDFGA
12CDF
13
Sheet1


After the macro:


Excel 2007
ABCDEFGHIJKL
1AASDAASDREWEREWQ
2AREWBFGSFDGADSF
3AERCASDDFGADF
4AEWQ
5XXX
6BFG
7BSFDG
8BADSF
9XXX
10CASD
11CDFGA
12CDF
13
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ReorgData()
' hiker95, 11/11/2014, ME817558
Dim a As Variant, o As Variant
Dim i As Long, j As Long, k As Long, c As Long
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
a = Range(Cells(1, 1), Cells(lr, lc))
ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "XXX" Then
      If .exists(a(i, 1)) Then
        k = .Item((a(i, 1)))
        For c = 1 To UBound(a, 2)
          If IsEmpty(o(k, c)) Then
            o(.Item((a(i, 1))), c) = a(i, c)
          End If
        Next c
      Else
        j = j + 1
        .Item((a(i, 1))) = j
        For k = 1 To UBound(a, 2)
          o(j, k) = a(i, k)
        Next k
      End If
    End If
  Next i
End With
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)).Value = o
Columns(lc + 3).Resize(, UBound(o, 2)).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
Very thorough answer! And yes, that code does technically do exactly what I asked for, so thank you haha. I was hoping it'd be a little simpler to do so I could apply it easily to my workbook but unfortunately your solution is a little above me to be able to translate to my means. So I suppose I should share more detail.
I've got 7am to 8pm broken down into 5 minute intervals on a timeline, and events are merged cells in the appropriate location on the timeline, stair stepping like I said above.
Here's a screenshot of my worksheet:
L6L5JU3.png





And here's what I want it to look like flattened out:
6gMKzN4.png


I was hoping there was a simple way to flatten the rows into each other, looks like it might be a touch more complicated through.
 
Upvote 0
Technowolf,

Thanks for the screenshots.

I was hoping there was a simple way to flatten the rows into each other

I could do it with a macro, but, I will have to see your actual workbook/worksheet.

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.
 
Upvote 0
Unfortunately I'm not able to share the entire workbook since it contains sensitive company data. What you can see in the screenshots though should provide all the information needed.
 
Upvote 0
Technowolf,

What you can see in the screenshots though should provide all the information needed.

I am not able to determine the extent of your dataset by the picture/graphic you have supplied.

And, I prefer to write macro code by using good examples of the raw data for testing. This way, I can usually solve a request on the first try.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
As I stated my spreadsheet contains sensitive company data so I'm sorry I'm not able to paste it all over the Internet. It's a bit surprising (and a touch rude tbh) to be told "go hope someone else will help you" because I can't share confidential information, but if that's how you wish to be then I suppose that's the option I'll take. I need to know how to flatten rows, the screen shots I provided are just as informative as my original post. I will look elsewhere for my solution since you are unable to assist me.
 
Upvote 0
As I stated my spreadsheet contains sensitive company data so I'm sorry I'm not able to paste it all over the Internet. It's a bit surprising (and a touch rude tbh) to be told "go hope someone else will help you" because I can't share confidential information, but if that's how you wish to be then I suppose that's the option I'll take. I need to know how to flatten rows, the screen shots I provided are just as informative as my original post. I will look elsewhere for my solution since you are unable to assist me.

I think you should read what he wrote again. Nothing rude there, actually quite helpful. He's telling you the bump part because it will bring it to someone's attention to look at it, otherwise they might ignore it thinking someone else is working on it. I think you should appreciate his effort in writing the code that he did, free of charge. BTW he didn't write "go hope someone else will help you", that's tbh rude to insinuate that he did.
 
Last edited:
Upvote 0
As I stated my spreadsheet contains sensitive company data so I'm sorry I'm not able to paste it all over the Internet. It's a bit surprising (and a touch rude tbh) to be told "go hope someone else will help you" because I can't share confidential information, but if that's how you wish to be then I suppose that's the option I'll take. I need to know how to flatten rows, the screen shots I provided are just as informative as my original post. I will look elsewhere for my solution since you are unable to assist me.

Hey Technowolf,
. Please do not get the “Hump” because of the suggestion that you “Bump”. If you check out the FAQ, forum rules, Guidelines for posting etc. etc. you will see that “Bumping” is normal to get your thread “back up the list” as it were. If you do not make a habit of doing that too often or too quickly then you will sometimes get more help. The code that hiker95 kindly gave is another one I am going through and learning from. Does the job nicely. You should be very grateful for that initial perfect (free) solution..
. Admittedly as he is using an array type solution it is somewhat more difficult to see what is going on by, for example, stepping through the code with F8 and seeing wot happens in the spreadsheet. This is because nothing happens until the end when the final array is pasted in. You can go some way to overcome this problem by setting a watch on things in the watch window, Variant o in particular, which becomes this output array. Admittedly it is difficult to see on a screen as the array increases in size. I would have maybe asked him if he could do a spreadsheet filling type solution in addition to an array filing solution to make it easier to understand and then apply easily to a particular situation. I expect the array in conjunction with the more advanced using of the Microsoft Runtime Scripting stuff was simply an attempt to give you an optimum fast professional solution. But I accept that makes it difficult to understand if as me you are still learning.
. The point with asking for actual data was very reasonable and as he said you could go through changing manually sensitive data. A very small price to pay for getting back so much good (free) help. Of course he could type in manually made up data, or something from your big screen shots but that would take him a long time and he is very busy doing such good work helping so many people here. As he said having a good representative set of data initially usually makes it possible to come up with a good solution first time. I have learnt that by the small attempts I have made to answer threads. Having said that hiker95’s code has been made as flexible as possible given the info that you gave him. I did some simple changes to the original data and it still works.
. Stick with it, have another go to understand the code, and/ or get the extra info prepared showing a good representative selection of what you have and importantly what it should look like after running the macro and present that in a way that can be copied into a spreadsheet (or as hiker95 suggested upload an Excel file with maybe wot you have in one sheet and what you want on another sheet) and then try your “Luck with a Bump!”
Alan
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top