Previous Topic Next Topic

Combine data of Multiple cells into one cell

Closed
AJ - Apr 22, 2010 at 08:38 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 22, 2010 at 02:23 PM
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 responses

Answer 1 / 2
Trowa
Apr 22, 2010 at 09:19 AM
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
AJ
Apr 22, 2010 at 11:29 AM
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
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 22, 2010 at 12:42 PM 
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
AJ
Apr 22, 2010 at 01:11 PM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 22, 2010 at 01:29 PM
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
AJ
Apr 22, 2010 at 01:50 PM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 22, 2010 at 02:02 PM
I am sorry, I dont get you
0
AJ
Apr 22, 2010 at 02:14 PM
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

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses