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
0
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
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 
0
Dear rizvisa1,
This macro is awesome and provides result in a great speed too. The help is highly appreciated.

But there is a minor issue with this.
It is leaving the content of first cell in column 2 after moving to the column 3 of [0] /[D] Line, whereas this should also have removed.

For example:

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

Result:
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 W: www.hintonburdick.com
1
1
1

Should be:
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

Please advise.

Thank you and Best Regards - AJ
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
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
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am sorry, I dont get you
0
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
0