Concatenating column B values if left adjacent cell is empty

[JayNixon]

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! :)

[#2]

@JayNixon 

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)))))

[#3]

@JayNixon 

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

[#4]

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.

[#5]

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.

[#6]

@JayNixon 

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/

[#7]

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

[#8]

@JayNixon 

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.

[#9]

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. 

[#10]

@JayNixon 

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

[#11]

@JayNixon 

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.

[#12]

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

[#13]

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....

[#14]

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

[#15]

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

[#16]

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?

[#17]

@JayNixon 

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

[#18]

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.

[#19]

@JayNixon 

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.

[#20]

@JayNixon 

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.

[#21]

I feel like I am missing something very simple that is just going over my head here. My data headers "ComputerName"in column A and "Application" in column B are in row 1. Data starts in row 2. Column B has no blank cells but Column A has many blank cells. I had deleted the duplicate server names. To me, it looks almost identical to the sample I submitted.

For whatever reason, when I run the macro it just deletes all the data. I ran the script in the sample I gave you. It worked there. It will not work on my actual sheet. It hangs for a second or so then just deletes everything. I am sorry.

I wish I could understand this better and apologize if it feels like you are talking to a 5 year old! lol.

[#22]

If you select column A with your raw data, how many values are there? It should say something like Count: 6267 at the right of the status bar at the bottom of the application window.

[#23]

Currently 235493 entries on column A. @Brad_Yundt 

[#24]

Although 90% or more are empty cells. I am not sure why they are registering as entries if they are empty.

[#25]

@JayNixon 

Might you have empty strings or spaces in those "blank" cells? If you do, the macro won't work right.

[#26]

You can test whether a cell is truly blank using a formula like:

=ISBLANK(A2)

My guess is that many of your column A cells look blank but actually contain an invisible value, such as a single quote, space, non-breaking space, Tab or Carriage Return character.

[#27]

I formatted the empty cells and tested them with the =isblank and they all came up false, except for of course the cells with actual values. Although after deleting that =isblank column I still was unable to get the macro to run correctly. It is still deleting all the data in the worksheet. I am not sure what I am doing wrong. Will troubleshoot a bit more but I thought you might want an update. I also checked column B for blanks and came up with none as well. Thanks again Brad.

[#28]

Here is a couple screenshots of the macro with a before and after running.

@Brad_Yundt 

[#29]

@JayNixon,

I would like test my code on your actual workbook. It's OK if you replace all the data with the letter "x" as long as you can reproduce the problem. You can post a workbook in this thread, or you could email it to me at first initial last name at my ISP, which is alum dot mit dot edu. I would then like to try reproducing the problem. Fixing the problem is easy if I can reproduce it.

It's possible that there is a limitation of Excel VBA present in your version of Excel that is not in mine. All my testing so far has been on 64-bit Excel 2016/Office 365 running on Windows 10--but I have other versions available for testing (both Mac and Windows, 32-bit and 64-bit, 2007 to date).

Please tell me:

Which version of Excel do you use, Mac or Windows?

If Windows, is 2007, 2010, 2013, 2016 or 2019?

Is it 32-bit or 64-bit?

[#30]

I am currently running on Windows 7 and using MSOffice 365 ProPlus ver. 16.0.9126.2315

I have found that if I run the macro on smaller portions of data, it will run just fine. I am most the way through my document by now.

[#31]

Thanks for sending me the file. I can reproduce the problem with my Excel 2016/Office 365 64-bit.

Excel cells can hold a maximum of 32,767 characters. Your problem server has 86,510 characters worth of software. I am guessing an overflow occurs that results in the loss of your data.

To test this hypothesis, I modified the code to truncate the software list to the first 32,767 characters. Now the macro seems to work.

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) = Left$(s, 32767)
        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) = Left$(s, 32767)

rg.ClearContents
rg.Resize(nData, 2).Value = vResults
End Sub

Please try running the revised macro on your full dataset. If it works, we can then shift discussion to how best to handle the text in the overflow. Perhaps additional columns or rows?

[#32]

It sounds like it will work. I will be able to try it in a couple hours or so and will report back.

As far as the overflow, additional rows are acceptable.@Brad_Yundt 

[#33 · Accepted solution]

@JayNixon

I revised the macro to allow up to 200 more rows of results in case of overflow, as well as up to 165 characters in next pass through the concatenation loop. When an overflow situation is detected, the server name will be repeated as often as needed to get all software names listed on additional rows.

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))
nData = nData + 200 'Allow for servers whose software list exceeds 32,767 characters
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) = Left$(s, 32767)
        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
    
    'Overflow occurs if you put more than  32,767 characters in a cell. This block allows up to 165 characters (plus delimiter) in next pass through loop.
    If Len(s) > 32600 Then
        If (i < n) And (vData(i + 1, 1) = "") Then
            vResults(k, 2) = s
            vResults(k + 1, 1) = vResults(k, 1)
            s = ""
            k = k + 1
        End If
    End If
Next
If s <> "" Then vResults(k, 2) = Left$(s, 32767)

rg.ClearContents
rg.Resize(nData, 2).Value = vResults
End Sub

[#34]

You have done it! Thank you for your dedication to this issue of mine. This works perfectly. @Brad_Yundt 

[#35]

@JayNixon 

If not a secret, what's the reason keep list of server software in so long strings instead of columns cell?

[#36]

@Sergei Baklan 

I am importing this into a sharepoint webpage through the excel app and if I kept all the applications in columns then some of the servers would have 100+ columns. I am positive there is a better way to accomplish my task but am a new intern and I lack the knowledge to do so. :/

[#37]

@JayNixon 

I see, thank you for the explanation. Perhaps servers in columns will be more compact and searchable, but it all depends on your concrete data.

[#38]

Yeah, unfortunately I am working with such a large amount of data here that it doesn't want to cooperate by making it easy and convenient to read. There are thousands of servers and the software includes everything from Google Chrome to .net framework updates to security updates for various tools. But what I have now is the easiest to search through I believe. I can query the inventory for a server and then it will just show me the column(s) of data associated with it. From there I can just Ctrl+F to search more specifically. Thanks for trying to help me with my issue. I appreciate it. I have much to learn!!

@Sergei Baklan