Need help with Concatenate function

  • Thread starter Thread starter Snowy
  • Start date Start date
S

Snowy

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus


I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy
 
Hello,

I have assumed that your data are in columns A to B
I have assumed that your data begin at row 2
(example : A2=0023.00 B2 =Brucellosis)
I have assumed that a blank row divides each set of data.
I have assumed that the cells just above your data are empty
(A1=B1=empty)

Into C2, put this formula:
=IF(B2="","",IF(C1="",B2,IF(ISNUMBER(FIND(" - ",C1)),LEFT(C1,FIND(" -
",C1)+2) & B2,C1 & " - " & B2)))

Drag down the formula to the end of your data

Does this formula help you ?

---------------------------------------------------------------------------------
"Snowy" a écrit dans le message de groupe de discussion :
(e-mail address removed)...

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus


I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy
 
You did not tells us if you wanted to fix the data in place or if you wanted
the concatenated text placed in another cell; I have assumed you wanted to
fix the data in place (that is, replace the existing data with the fixed
data). Give this macro a try...

Sub FixItems()
Dim X As Long, LastRow As Long, A As Range, Blanks As Range
Const StartRow As Long = 2
Const DataCol As String = "A"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Set Blanks = Range(Cells(StartRow, DataCol), Cells(LastRow, _
DataCol)).SpecialCells(xlCellTypeConstants)
For Each A In Blanks.Areas
For X = A(1).Row + 1 To A(1).Row + A.Rows.Count - 1
Cells(X, DataCol).Value = Replace(Cells(X, DataCol).Value, " ", _
" " & Mid(A(1).Value, InStr(A(1). _
Value, " ") + 1) & " - ", , 1)
Next
Next
End Sub

Make sure you change the StartRow and DataCol constants (in the Const
statements) to reflect your actual data layout.

Rick Rothstein (MVP - Excel)




"Snowy" wrote in message

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus


I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy
 
Hello,

I have assumed that your data are in columns A to B
I have assumed that your data begin at row 2
(example : A2=0023.00    B2 =Brucellosis)
I have assumed that a blank row divides each set of data.
I have assumed that the cells just above your data are empty
(A1=B1=empty)

Into C2, put this formula:
=IF(B2="","",IF(C1="",B2,IF(ISNUMBER(FIND(" - ",C1)),LEFT(C1,FIND("-
",C1)+2) & B2,C1 & " - " & B2)))

Drag down the formula to the end of your data

Does this formula help you ?

---------------------------------------------------------------------------------
"Snowy"  a crit dans le message de groupe de discussion :
(e-mail address removed)...

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus

I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy
Click to expand...

Hello
Thanks for your reply,

I am explaning what I want in details.

I have 2 columns A & B. I dont want to do anything with column A.

In column B I have following data.

Eg. Column B
Skin conditions
Rheumatoid arthritis
Other rheumatic disorders
Congenital dislocation of hip
Maternal postnatal screening
Chemical poisoning and other contamination
Screening for:

Column C- Output
Skin conditions
Skin conditions - Rheumatoid arthritis
Skin conditions - Other rheumatic disorders
Skin conditions - Congenital dislocation of hip
Skin conditions - Maternal postnatal screening
Skin conditions - Chemical poisoning and other contamination
Skin conditions - Screening for:

Look at the world "Skin conditions" . "Skin condition" is the value
after blank cell and I want to append value after blank cell to
remaining values till next blank cell appears.

Do this until entire column ends. There are about 30K rows data out of
which 12K blank rows.
 
Hello
Thanks for your reply,

I am explaning what I want in details.

I have 2 columns A & B. I dont want to do anything with column A.

In column B I have following data.

Eg. Column B
Skin conditions
Rheumatoid arthritis
Other rheumatic disorders
Congenital dislocation of hip
Maternal postnatal screening
Chemical poisoning and other contamination
Screening for:

Column C- Output
Skin conditions
Skin conditions - Rheumatoid arthritis
Skin conditions - Other rheumatic disorders
Skin conditions - Congenital dislocation of hip
Skin conditions - Maternal postnatal screening
Skin conditions - Chemical poisoning and other contamination
Skin conditions - Screening for:

Look at the world "Skin conditions" . "Skin condition" is the value
after blank cell and I want to append value after blank cell to
remaining values till next blank cell appears.

Do this until entire column ends. There are about 30K rows data out of
which 12K blank rows.
 
Okay, given this new information, give this macro a try...

Sub CreatePrefixedItems()
Dim X As Long, LastRow As Long, A As Range, Blanks As Range
Const StartRow As Long = 2
Const DataCol As String = "B"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Set Blanks = Range(Cells(StartRow, DataCol), Cells(LastRow, _
DataCol)).SpecialCells(xlCellTypeConstants)
For Each A In Blanks.Areas
A(1).Offset(0, 1).Value = A(1).Value
For X = A(1).Row + 1 To A(1).Row + A.Rows.Count - 1
Cells(X, DataCol).Offset(0, 1).Value = A(1).Value & " - " & _
Cells(X, DataCol).Value
Next
Next
End Sub

Again, set the StartRow constant to the actual row number containing your
first item in Column B (I set the DataCol constant to "B" for you).

Rick Rothstein (MVP - Excel)




"Snowy" wrote in message

Hello
Thanks for your reply,

I am explaning what I want in details.

I have 2 columns A & B. I dont want to do anything with column A.

In column B I have following data.

Eg. Column B
Skin conditions
Rheumatoid arthritis
Other rheumatic disorders
Congenital dislocation of hip
Maternal postnatal screening
Chemical poisoning and other contamination
Screening for:

Column C- Output
Skin conditions
Skin conditions - Rheumatoid arthritis
Skin conditions - Other rheumatic disorders
Skin conditions - Congenital dislocation of hip
Skin conditions - Maternal postnatal screening
Skin conditions - Chemical poisoning and other contamination
Skin conditions - Screening for:

Look at the world "Skin conditions" . "Skin condition" is the value
after blank cell and I want to append value after blank cell to
remaining values till next blank cell appears.

Do this until entire column ends. There are about 30K rows data out of
which 12K blank rows.
 
Actually, to help speed things along, use this variation of the macro I just
posted instead...

Sub CreatePrefixedItems()
Dim X As Long, LastRow As Long, A As Range, Blanks As Range
Const StartRow As Long = 2
Const DataCol As String = "B"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
Set Blanks = Range(Cells(StartRow, DataCol), Cells(LastRow, _
DataCol)).SpecialCells(xlCellTypeConstants)
For Each A In Blanks.Areas
A(1).Offset(0, 1).Value = A(1).Value
For X = A(1).Row + 1 To A(1).Row + A.Rows.Count - 1
Cells(X, DataCol).Offset(0, 1).Value = A(1).Value & " - " & _
Cells(X, DataCol).Value
Next
Next
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)
 
Hello,

Could you tell me what the problem is, please ?

Charaabeuh.



----------------------------------------------------------------------
"Snowy" a écrit dans le message de groupe de discussion :
(e-mail address removed)...

Hello
Thanks for your reply,

I am explaning what I want in details.

I have 2 columns A & B. I dont want to do anything with column A.

In column B I have following data.

Eg. Column B
Skin conditions
Rheumatoid arthritis
Other rheumatic disorders
Congenital dislocation of hip
Maternal postnatal screening
Chemical poisoning and other contamination
Screening for:

Column C- Output
Skin conditions
Skin conditions - Rheumatoid arthritis
Skin conditions - Other rheumatic disorders
Skin conditions - Congenital dislocation of hip
Skin conditions - Maternal postnatal screening
Skin conditions - Chemical poisoning and other contamination
Skin conditions - Screening for:

Look at the world "Skin conditions" . "Skin condition" is the value
after blank cell and I want to append value after blank cell to
remaining values till next blank cell appears.

Do this until entire column ends. There are about 30K rows data out of
which 12K blank rows.
 
Back
Top