|
Need help with Concatenate function
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 |
Need help with Concatenate function
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 |
Need help with Concatenate function
On Jan 9, 5:23*pm, "Charabeuh" wrote:
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 : ... 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 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. |
Need help with Concatenate function
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. On Jan 9, 10:41*pm, "Rick Rothstein" wrote: 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 |
Need help with Concatenate function
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. On Jan 9, 10:41 pm, "Rick Rothstein" wrote: 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 |
Need help with Concatenate function
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) |
Need help with Concatenate function
Hello,
Could you tell me what the problem is, please ? Charaabeuh. ---------------------------------------------------------------------- "Snowy" a écrit dans le message de groupe de discussion : ... 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. |
Need help with Concatenate function
On Sun, 9 Jan 2011 00:52:56 -0800 (PST), Snowy wrote:
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 I note that your sets, although separated by blank rows, are also characterized by the integer portion of the ID being the same. Ordinarily, I would "key" off that. But looking at your later clarification, it seems you really want to key off the blank entry. Accordingly, assuming that B1 is blank, and your first entry is in B2, C2: =IF(B2="","",IF(OFFSET(B2,-1,0)="",B2,LOOKUP(2,1/($B$1:B2=""),$B$2:B3)&" - " & B2)) and fill down as far as needed. The references will self adjust. If you have to change the cell references, note that for the LOOKUP function, the lookup_vector and result_vector are offset by 1. |
| All times are GMT +1. The time now is 04:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com