Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
Concatenating column B values if left adjacent cell is empty

- Home
- :
- Excel
- :
- General Discussion
- :
- Concatenating column B values if left adjacent cell is empty

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 08:32 AM

I need to combine cells in column B if the value in the cell to the left is blank while also deleting the blank cell to the left to close any gaps between cells in Column A. The best way for me to explain this is by showing an example. I need this:

**Column A Column B**

Test This

That

Other

Test2 Also

This

Test3 Please

Help

Me

To look like this

**Column A Column B**

Test This, That, Other

Test2 Also, This

Test3 Please, Help, Me

I am not quite sure how to word my question to search so I apologize if this has been asked many times! :)

Labels:

37 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 08:52 AM

If you want a formula to return your concatenated text and have Excel 2016 on Office 365 subscription (or Excel 2019), then you may copy down a TEXTJOIN formula:

=TEXTJOIN(",",TRUE,INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,IFERROR(MATCH(D3,A:A,0)-1,MAX(MATCH({"zzzzz",1E+307},B:B,1)))))

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 08:52 AM

It could be done with Power Query - query the date, fill down first column, group by it, add column with initial second column as list and extract it with separator

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 08:58 AM

Thanks @Brad_Yundt for your quick response here. I am trying this formula now. I am a novice when it comes to excel so I have a few questions.

I tried running that formula is column C and the return is #N/A. What do I need to change here?

Is column C the correct column?

Thanks in advance.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:01 AM

Thanks for responding so quickly @Sergei Baklan. I am pretty green when it comes to excel but have been working with it extensively in my new position. If you have the time, could you explain Power Query a bit more? I would like to know more about it from someone who has obviously used it.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:13 AM

Hi Jay,

there are a solution with help of the follwing macro:

Sub GroupData()

Dim lngRow As Long

Dim lngRowMax As Long

Dim lngz As Long

Dim lngzMax As Long

With Sheet1

.Range("G:H").ClearContents

.Range("G1:H1").Value = .Range("A1:B1").Value

lngz = 2

lngRowMax = .Range("B" & .Rows.Count).End(xlUp).Row

For lngRow = 2 To lngRowMax

If .Range("A" & lngRow).Value <> "" Then

.Range("G" & lngz).Value = .Range("A" & lngRow).Value

.Range("H" & lngz).Value = .Range("B" & lngRow).Value

lngz = lngz + 1

Else

lngzMax = .Range("H" & .Rows.Count).End(xlUp).Row

.Range("H" & lngzMax).Value = .Range("H" & lngzMax).Value & "," & .Range("B" & lngRow).Value

End If

Next lngRow

End With

End Sub

Best regards

Bernd

https://vba-tanker.com/

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:18 AM

If you prefer a macro to concatenate, then consider:

Sub Sequelize() Dim rg As Range, targ As Range Dim delimiter As String, s As String Dim i As Long, j As Long, n As Long delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count j = 1 For i = n To 1 Step -1 If rg.Cells(i, 1).Value <> "" Then rg.Rows(i + 1).Resize(j - 1).EntireRow.Delete If rg.Cells(i, 2).Value <> "" Then s = rg.Cells(i, 2).Value & delimiter & s If s <> "" Then rg.Cells(i, 2).Value = Left(s, Len(s) - Len(delimiter)) j = 1 s = "" Else j = j + 1 If rg.Cells(i, 2).Value <> "" Then s = rg.Cells(i, 2).Value & delimiter & s End If Next End Sub

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:20 AM

The formula was assuming that you already had the column A values listed as in your initial post.

When you subsequently changed your question, I wrote a macro to do the new request.

The attached workbook shows both the macro and the formula.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:36 AM

Thank you @Brad_Yundt. I am trying to test this now on my actual worksheet and the only problem I am having is that the sheet is 260,000 rows and my computer will freeze every time I try anything.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:43 AM

Please post a small sample workbook showing your actual data layout.

I propose to use array transfer for speed, and want to get the code right the first time. I therefore need to know the layout, extent of data and complicating factors (merged cells, worksheet protection, adjacent columns that must not be deleted, blank rows, etc).

Brad

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 09:45 AM

Power Query - you may start from Getting Started with Get & Transform in Excel ,it gives the main idea. Technology requires some time to invest, but you may start doing something practical from very first steps.

For this sample you may open the query (Data->Queries $ Connections, double click on query in the right pane) and check step by step what it's doing.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 10:13 AM

Going somewhat out on a limb here without a sample workbook to look at. I revised the code to use array transfer into VBA and back. It will run a lot faster as a result.

Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = s End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If Next If s <> "" Then vResults(k, 2) = s rg.ClearContents rg.Resize(nData, 2).Value = vResults End Sub

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 10:25 AM

I apoligize for the delay in response here @Brad_Yundt. I was having trouble with my laptop because excel was crashing often. I will attach a very small sample of the data I am working with. I am basically looking at all the software/applications installed on all the servers in my domain. I want all the applications belonging to each server to be in one cell block and be separated by a comma.

ServerA IE,Chrome,Windows Update, Intel driver, Edge, etc...

ServerB Chrome, Nvidia GPU Driver, Edge, etc....

Rather than what it currently is

ServerA IE

Chrome

Windows Update

Intel driver

Edge

etc...

Server B Chrome

Nvidia GPU Driver

Edge

etc....

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 10:45 AM

I just bookmarked this. I will read it on my next break. Thank you Sergei.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:05 AM

My most recent macro is working on your test workbook. I installed the code, added a button to call it in the attached workbook so you may test. The duplicate data off to the right is so I can easily restore the original condition.

Brad

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:13 AM

I just ran it on my actual worksheet and it deleted everything below row 1. I may not be executing this properly. I went to view macros, create, pasted in the macro from above, saved it, then ran that macro. I assume this is how I am supposed to do it but maybe I am incorrect. Also my actual worksheet has 260,000 rows of data but that shouldn't affect it, correct?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:18 AM

Here is the Power Query version. Applications are sorted alphabetically in the list.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:36 AM

I have never used Power Query. This is something I will have to research more as I haven't the slightest clue how to utilize it. I see it works beautifully. I will start my reading now.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:43 AM

Macro which Brad suggested works fine, but if you never used VBA it also takes time to learn how it works. VBA is much more universal, but, from my point of view, Power Query is more suitable for data transform tasks.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-15-2019 11:45 AM

The macro assumes your data starts in row 2 and that header labels are in row 1. It further assumes that you have no blank lines in your data, and that there are only two columns of data.

Your question said that you wanted to start with two columns of data in columns A and B, then end up with two columns of compacted results in columns A and B. That's what the macro does.

The third from the last statement in the macro deletes the existing data (except header labels). The next to last statement puts the compacted results starting in cell A2. If you want them moved elsewhere, that statement is easily changed.

I made 6270 copies of your data, filling the sheet past row 260000 and ran the macro. It completed work in less than a second.