Combining Cells in One Column Based on Another Column

B

branchard9211

New Member
Joined
Jul 18, 2014
Messages
6
Hi - I am hoping someone might be able to help me with this problem. I currently have a spreadsheet that has 4 columns. I want to review the information in column A and if it matches, I want to combine the cells in column D into one cell for the matches in column A. Here is an example of the information I have:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]123456[/TD]
[TD]ABC Company[/TD]
[TD]901[/TD]
[TD]0001[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]ABC Company[/TD]
[TD]901[/TD]
[TD]0099[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]ABC Company[/TD]
[TD]901[/TD]
[TD]HRA1[/TD]
[/TR]
[TR]
[TD]147258[/TD]
[TD]BBC Company[/TD]
[TD]901[/TD]
[TD]0001[/TD]
[/TR]
[TR]
[TD]147258[/TD]
[TD]BBC Company[/TD]
[TD]901[/TD]
[TD]0002[/TD]
[/TR]
[TR]
[TD]147258[/TD]
[TD]BBC Company[/TD]
[TD]901[/TD]
[TD]0099[/TD]
[/TR]
[TR]
[TD]159951[/TD]
[TD]CBC Company[/TD]
[TD]901[/TD]
[TD]0001[/TD]
[/TR]
[TR]
[TD]159951[/TD]
[TD]CBC Company[/TD]
[TD]901[/TD]
[TD]0099[/TD]
[/TR]
</TBODY>[/TABLE]


So basically I want to review column A and any # in that column matches, I want to combine the cells from column D into one cell. So I would want the first sequence to catch that A1, A2, and A3 match... so then it would combine D1, D2, and D3 into one cell that would read "0001/0099/HRA1". Is this possible?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
branchard9211,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


With your raw data already sorted/grouped in column A.


Sample raw data:


Excel 2007
ABCD
1123456ABC Company9010001
2123456ABC Company9010099
3123456ABC Company901HRA1
4147258BBC Company9010001
5147258BBC Company9010002
6147258BBC Company9010099
7159951CBC Company9010001
8159951CBC Company9010099
9
Sheet1


After the macro:


Excel 2007
ABCD
1123456ABC Company9010001/0099/HRA1
2147258BBC Company9010001/0002/0099
3159951CBC Company9010001/0099
4
5
6
7
8
9
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, 07/18/2014, ME792496
Dim lr As Long, r As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n > 1 Then
    Range("D" & r) = Join(Application.Transpose(Range("D" & r & ":D" & (r + n) - 1)), "/")
    Range("A" & r + 1 & ":A" & r + n - 1).ClearContents
  End If
  r = r + n - 1
Next r
On Error Resume Next
Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Columns.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
Wow! Thank you so much. Worked like a charm.

I am using Microsoft Office 2010 and Windows 7.
Could this be modified to organize more than just column D based on column A?
If I had a 5th column that had additional information that needed to be combined based on column A, could it be added to this same code so that it combines more columns?
 
Upvote 0
branchard9211,

Wow! Thank you so much. Worked like a charm.
Click to expand...

Thanks for the feedback.

You are very welcome. Glad I could help.


Could this be modified to organize more than just column D based on column A?
If I had a 5th column that had additional information that needed to be combined based on column A, could it be added to this same code so that it combines more columns?
Click to expand...

Probably.

I would have to see screenshots of the new dataset, before, and, after a macro (manually formatted by you) with the results you are looking for.

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
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
branchard9211 said:
Wow! Thank you so much. Worked like a charm.

I am using Microsoft Office 2010 and Windows 7.
Could this be modified to organize more than just column D based on column A?
If I had a 5th column that had additional information that needed to be combined based on column A, could it be added to this same code so that it combines more columns?
Click to expand...



Actually... I looked at my next spreadsheet and I don't think I accurately said what I was needing out of this one. This one as 5 columns and I think is a bit trickier. I need to condense it down to one row for each # in column A. But I need to combine info in column D that is duplicated and info in column E that is duplicated.
Here is an example of what this sheet looks like:
[TABLE="width: 576"]
<TBODY>[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]DDP00903</SPAN>[/TD]
[/TR]
[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]MBPW8624</SPAN>[/TD]
[/TR]
[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]MBSW8868</SPAN>[/TD]
[/TR]
[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]DDP00903</SPAN>[/TD]
[/TR]
[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBPW8624</SPAN>[/TD]
[/TR]
[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSW8868</SPAN>[/TD]
[/TR]
[TR]
[TD]100278 </SPAN>[/TD]
[TD]Industrial Maintenance & Engineering Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSEF399</SPAN>[/TD]
[/TR]
[TR]
[TD]100278 </SPAN>[/TD]
[TD]Industrial Maintenance & Engineering Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSES399</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR1</SPAN>[/TD]
[TD]DDP16643</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR1</SPAN>[/TD]
[TD]MBSEE729</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR1</SPAN>[/TD]
[TD]MBSW8727</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]DDP16643</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSEE729</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSW8727</SPAN>[/TD]
[/TR]
[TR]
[TD]100448 </SPAN>[/TD]
[TD]Rockin L Rentals Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]DDP16155</SPAN>[/TD]
[/TR]
[TR]
[TD]100448 </SPAN>[/TD]
[TD]Rockin L Rentals Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSW2366</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]DDCP0015</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]MBPEEA60</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]MBPW9973</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR </SPAN>[/TD]
[TD]VEMPSC20</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]DDCP0015</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBPEEA60</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBPW9973</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]VEMPSC20</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]



So I only need the info in column D listed 1 time in the same cell like PPO/CBR.
And the same thing for column E... DDCP0015/MBPEEA60/MBPW9973/VEMPSC20
 
Upvote 0
I posted my column above before I saw your post. I can't download anything and install it on the computer I'm working on b/c it's not my PC :) But this is what i would want the above to actually look like when I'm done:

[TABLE="width: 737"]
<TBODY>[TR]
[TD]100270 </SPAN>[/TD]
[TD]Jeffco Flooring & Supply Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR/PPO</SPAN>[/TD]
[TD]DDP00903/MBPW8624/MBSW8868</SPAN>[/TD]
[/TR]
[TR]
[TD]100278 </SPAN>[/TD]
[TD]Industrial Maintenance & Engineering Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]MBSEF399</SPAN>[/TD]
[/TR]
[TR]
[TD]100346 </SPAN>[/TD]
[TD]Trucolor Litho Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR1/PPO</SPAN>[/TD]
[TD]DDP16643/MBSEE729/MBSW8727</SPAN>[/TD]
[/TR]
[TR]
[TD]100448 </SPAN>[/TD]
[TD]Rockin L Rentals Inc </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]PPO </SPAN>[/TD]
[TD]DDP16155/MBSW2366</SPAN>[/TD]
[/TR]
[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation </SPAN>[/TD]
[TD]901</SPAN>[/TD]
[TD]CBR/PPO</SPAN>[/TD]
[TD]DDCP0015/MBPEEA60/MBPW9973/VEMPSC20</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
Using your code and adding a line to it for column E, I can make them look like this:

[TABLE="width: 1271"]
<TBODY>[TR]
[TD]100462 </SPAN>[/TD]
[TD]DBS Corporation [/TD]
[TD]901</SPAN>[/TD]
[TD]CBR /CBR /CBR /CBR /PPO /PPO /PPO /PPO </SPAN>[/TD]
[TD] DDCP0015/MBPEEA60/MBPW9973/VEMPSC20/DDCP0015/MBPEEA60/MBPW9973/VEMPSC20</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]


But I don't know how to get rid of the duplicated items in column D & E.
 
Upvote 0
branchard9211,

New sample raw data:


Excel 2007
ABCDE
1100270Jeffco Flooring & Supply Inc901CBRDDP00903
2100270Jeffco Flooring & Supply Inc901CBRMBPW8624
3100270Jeffco Flooring & Supply Inc901CBRMBSW8868
4100270Jeffco Flooring & Supply Inc901PPODDP00903
5100270Jeffco Flooring & Supply Inc901PPOMBPW8624
6100270Jeffco Flooring & Supply Inc901PPOMBSW8868
7100278Industrial Maintenance & Engineering Corporation901PPOMBSEF399
8100278Industrial Maintenance & Engineering Corporation901PPOMBSES399
9100346Trucolor Litho Inc901CBR1DDP16643
10100346Trucolor Litho Inc901CBR1MBSEE729
11100346Trucolor Litho Inc901CBR1MBSW8727
12100346Trucolor Litho Inc901PPODDP16643
13100346Trucolor Litho Inc901PPOMBSEE729
14100346Trucolor Litho Inc901PPOMBSW8727
15100448Rockin L Rentals Inc901PPODDP16155
16100448Rockin L Rentals Inc901PPOMBSW2366
17100462DBS Corporation901CBRDDCP0015
18100462DBS Corporation901CBRMBPEEA60
19100462DBS Corporation901CBRMBPW9973
20100462DBS Corporation901CBRVEMPSC20
21100462DBS Corporation901PPODDCP0015
22100462DBS Corporation901PPOMBPEEA60
23100462DBS Corporation901PPOMBPW9973
24100462DBS Corporation901PPOVEMPSC20
25
Sheet1


After the new macro:


Excel 2007
ABCDE
1100270Jeffco Flooring & Supply Inc901CBR/PPODDP00903/MBPW8624/MBSW8868
2100278Industrial Maintenance & Engineering Corporation901PPOMBSEF399/MBSES399
3100346Trucolor Litho Inc901CBR1/PPODDP16643/MBSEE729/MBSW8727
4100448Rockin L Rentals Inc901PPODDP16155/MBSW2366
5100462DBS Corporation901CBR/PPODDCP0015/MBPEEA60/MBPW9973/VEMPSC20
6
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).

Code: 
Sub ReorgDataV2()
' hiker95, 07/18/2014, ME792496
Dim lr As Long, r As Long, n As Long
Dim d As Range, drng As Range, e As Range, erng As Range
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n > 1 Then
    Set drng = Range("D" & r & ":D" & (r + n) - 1)
    With CreateObject("Scripting.Dictionary")
      .CompareMode = vbTextCompare
      For Each d In drng
        If Not .Exists(d.Value) Then
          .Add d.Value, d.Value
        End If
      Next
      Range("D" & r) = Join(.Keys, "/")
      Set d = Nothing
    End With
    Set erng = Range("E" & r & ":E" & (r + n) - 1)
    With CreateObject("Scripting.Dictionary")
      .CompareMode = vbTextCompare
      For Each e In erng
        If Not .Exists(e.Value) Then
          .Add e.Value, e.Value
        End If
      Next
      Range("E" & r) = Join(.Keys, "/")
      Set e = Nothing
    End With
    Range("A" & r + 1 & ":A" & r + n - 1).ClearContents
  End If
  r = r + n - 1
Next r
On Error Resume Next
Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

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 ReorgDataV2 macro.
 
Upvote 0
I think you brilliant! How on earth do you know how to do all this? I took a few classes in programming and decided it wasn't for me. I dabble around a little but not much. I can't thank you enough for your help. I might be calling upon you again :)
 
Upvote 0
branchard9211,

Thanks for the feedback.

You are very welcome. Glad I could help.

How on earth do you know how to do all this? I took a few classes in programming and decided it wasn't for me. I dabble around a little but not much.
Click to expand...

While I was working I had to solve problems for myself, and, others in SuperCalc, Lotus 1-2-3, a low level programming language - Assembler Programming, and, finally in Excel. I was able to find/create solutions that made me more productive, and, then other people started asking for more, and, more help. I also won software contests at work.

Practice, practice, and, more of the same.

I might be calling upon you again
Click to expand...

Come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Combining PDF files from a folder using a list from excel (not importing to excel, combining as a single PDF)
Replies
2
Views
268
Patrick Desarzens
P
M
  • Solved
Transfer comments column from report to external sheet without macros
Replies
4
Views
318
melkontar
M
A
  • Solved
Hide Column with multiple command buttons
2
Replies
10
Views
303
arisomar
A
Guybrush Threepwood
  • Question Question
Using Filter for Criteria in Multiple Columns and a Specific Row Range?
Replies
0
Views
409
Guybrush Threepwood
Guybrush Threepwood
W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
323
waffle
W

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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