Combine data of Multiple cells into one cell [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have a list of Records in Excel sheet in a following format. Each record contains more than one Row. Each new Record starts from number Zero [0].
Note: There are two columns in input and in the output result should appear on third column of zero [0]
0
1
1 W: www.hintonburdick.com
1 NM: Robert Cox, CPA
1 NM: Todd Hess, CPA
1 NM: Jim Wilson, CPA

I need a help in creating a Macro or a Function so that the above input is changed to Following:
0 W: www.hintonburdick.com|NM: Robert Cox, CPA|NM: Todd Hess, CPA|NM: Jim Wilson, CPA
1
1 W: www.hintonburdick.com
1 NM: Robert Cox, CPA
1 NM: Todd Hess, CPA
1 NM: Jim Wilson, CPA

2 replies

Hi AJ,

It's not clear to me how you made up your sheet, but I put your 4 lines of data in the range A3:A6. Then use any cell to input the following function:
=A3 & "|" & A4 & "|" & A5 & "|" & A6


Adjust the function to suit your layout.

Best regards,
Trowa
Dear Trowa,

First of all thank you so much for attending and providing a solution. I have realized that the way I have put in the question is not easy to understand. Let me explain the question:
Basically I have two columns containing the data.
1. Each record starts from a zero [0] or [D] in first column.
-The rows under it in second Column may or may not contain data.
2. The new record once again begins with zero [0] or [D].

3. Now the help I require is that I want to concatenate or combine the information printed under
each [0] or [D] line one column number 3, where [0] or [D] is appearing.
- In addition to this I need to put a separator between the information picked from each row,
using a pipe sign [|].

Sample:
Column 1 Column 2 Column 3
0
1
1 W: www.hintonburdick.com
1 NM: Robert Cox, CPA
1 NM: Todd Hess, CPA
1 NM: Jim Wilson, CPA

Solution:
Column 1 Column 2 Column 3
0 W: www.hintonburdick.com|NM: Robert
Cox, CPA|NM: Todd Hess, CPA|NM: Jim Wilson, CPA
1
1
1
1
1

Note: The rows from where the data was moved to the [0] or [D] line should not be deleted.
Hope the above explaination is easy to understand and would help you in understanding the requirement.

Your help on this would be highly appreciated.

Best Regards - AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Sub dataAlign() 
Dim lMaxRows As Long 
Dim iCounter As Long 
Dim iRecRow As Long 

    'max data row 
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row 
     
    'processing all rows 
    For iCounter = 1 To lMaxRows 
     
        ' if the value in column A of processed row is 0, then its a new record 
        If Cells(iCounter, "A") = 0 Then 
            iRecRow = iCounter 
         
        'if the value in column A of processed row is 1 and there is a value in column B on same row, then add this 
        ' to start of record marked by 0 
        ElseIf Cells(iCounter, "A") = 1 And Cells(iCounter, "B") <> "" Then 
             
            ' if the current value is "" 
            If Cells(iRecRow, "C") = "" Then 
             
                Cells(iRecRow, "C") = Cells(iCounter, 2) 
                Cells(iCounter, "B") = "" 'wipe the record 
             
            Else 
                 
                Cells(iRecRow, "C") = Cells(iRecRow, "C") & "|" & Cells(iCounter, "B") 
                Cells(iCounter, "B") = "" 'wipe the record 
             
            End If 
        End If 
     
    Next 

End Sub 
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
My Bad. I have modified the code. Try again. To the previous code, I have added this one line of code

Cells(iCounter, "B") = "" 'wipe the record
Dear Rizviza1

I have more request.

Based on the above result produced by [dataAlign] Macro, is there a way break the pipe [separated] information in a row to a new row(s).

One under the another. The Pipes should not appear in the output.

Please advise.

Best Regards -
AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
I am sorry, I dont get you
Apologize Rizvisa1 my mistake, I should have included an example.

Sample:
Column1 Column2 Column 3
Row 1 0 W: www.hintonburdick.com|NM: Robert Cox, CPA|NM: Todd Hess, CPA|NM: Jim Wilson, CPA

Should be:
Column1 Column2 Column3
Row 1 0 W: www.hintonburdick.com
Row 2 NM: Robert Cox, CPA
Row 3 NM: Todd Hess, CPA
Row 4 NM: Jim Wilson, CPA

Hope the above example clarifies the doubt.

Thankyou ever so much.

Best Regards,
AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Ok I am now more confused. This is how you have it in the first place, So What exactly changed . All it seem is that you have removed "1" and have populated the row with o

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!