Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates entire
Let's say i have a column A and column B with a lot of cells with different
content. For every cell in A column with content , there is a B cell with content What i want is macro which searches for given key word in column A . If it finds it , it concatenates entire row (without spaces between content of different cells) and puts in in column C ( let's say ) The most important thing is where it puts it . And i give in example : key word : house A1 : garden B1 : basement A2 : house B2 : bedroom A3 : mother B3 : father A4 : house B4 : like a cottage Finds keyword in A2 , concatenates A2-B2 ( result - housebedroom ) and puts it in C1 ! Finds keyword in A4 , concatenates A4-B4( result - houselike a cottage ) and puts it in C2 ! so on ... In my case , as i said before , i don't need spaces between content from different cells . For what i use it , the concatenated result makes sense without spaces |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates entire
why don't you just use a simple VLOOKUP
if your keyword is in D1, and you search for it in A:A concatenatign to B:B the C1=D1 & VLOOKUP(D1,A:B,2,False) "andrei" wrote: Let's say i have a column A and column B with a lot of cells with different content. For every cell in A column with content , there is a B cell with content What i want is macro which searches for given key word in column A . If it finds it , it concatenates entire row (without spaces between content of different cells) and puts in in column C ( let's say ) The most important thing is where it puts it . And i give in example : key word : house A1 : garden B1 : basement A2 : house B2 : bedroom A3 : mother B3 : father A4 : house B4 : like a cottage Finds keyword in A2 , concatenates A2-B2 ( result - housebedroom ) and puts it in C1 ! Finds keyword in A4 , concatenates A4-B4( result - houselike a cottage ) and puts it in C2 ! so on ... In my case , as i said before , i don't need spaces between content from different cells . For what i use it , the concatenated result makes sense without spaces |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates entire
Andrei,
Why not use an IF function rather than a macro? You could place your key word (i.e. the "find' word) in D1 and use an IF funciton to "find" and concatenate the cells. For example, cell D1 has "house" in it and cell C1 has the following formula: =IF(A1=$D$1,A1&B1,""). Simply copy the formula in cell C1 down, change the text in D1 accordingly, and recalculate the cells (if you are not using Automatic calculation). (If you really feel you need the macro then post back to the thread that you need it, and I'll create a sample if someone else doesn't do so before I get to it). Best, Matthew Herbert "andrei" wrote: Let's say i have a column A and column B with a lot of cells with different content. For every cell in A column with content , there is a B cell with content What i want is macro which searches for given key word in column A . If it finds it , it concatenates entire row (without spaces between content of different cells) and puts in in column C ( let's say ) The most important thing is where it puts it . And i give in example : key word : house A1 : garden B1 : basement A2 : house B2 : bedroom A3 : mother B3 : father A4 : house B4 : like a cottage Finds keyword in A2 , concatenates A2-B2 ( result - housebedroom ) and puts it in C1 ! Finds keyword in A4 , concatenates A4-B4( result - houselike a cottage ) and puts it in C2 ! so on ... In my case , as i said before , i don't need spaces between content from different cells . For what i use it , the concatenated result makes sense without spaces |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates entire
Do you have to use a macro?
I'd just use a formula. I'd add a header row (new row 1) and plop the keyword into C1. Then put this in C2: =if(a2<$c$1,"",a2&b2) and drag down andrei wrote: Let's say i have a column A and column B with a lot of cells with different content. For every cell in A column with content , there is a B cell with content What i want is macro which searches for given key word in column A . If it finds it , it concatenates entire row (without spaces between content of different cells) and puts in in column C ( let's say ) The most important thing is where it puts it . And i give in example : key word : house A1 : garden B1 : basement A2 : house B2 : bedroom A3 : mother B3 : father A4 : house B4 : like a cottage Finds keyword in A2 , concatenates A2-B2 ( result - housebedroom ) and puts it in C1 ! Finds keyword in A4 , concatenates A4-B4( result - houselike a cottage ) and puts it in C2 ! so on ... In my case , as i said before , i don't need spaces between content from different cells . For what i use it , the concatenated result makes sense without spaces -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates ent
Thanks guys . Both functions work . I will try to manage with them . I
prefered a macro that moves the concatenated results in a column as i explained in first post for the following reason : The cells i have to process from 2 columns are from something like A1B1 , A9B9 , A12B12 , A25B25 going to something like A10000B10000 ( without a clear pattern that i have content every 10th cell , it's random ) . That's why i needed the results to be one below another in same column ( say from C1 to C100 ) If a macro can be done , many thanks in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates ent
I didn't notice you wanted it contiguous cells.
I'd add a filter and show the non-blanks, but if that doesn't help: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim DestCell As Range Dim myWord As String myWord = "house" With Worksheets("Sheet1") Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) Set DestCell = .Range("C1") End With For Each myCell In myRng.Cells With myCell If LCase(.Value) = LCase(myWord) Then DestCell.Value = .Value & .Offset(0, 1).Value Set DestCell = DestCell.Offset(1, 0) End If End With Next myCell End Sub andrei wrote: Thanks guys . Both functions work . I will try to manage with them . I prefered a macro that moves the concatenated results in a column as i explained in first post for the following reason : The cells i have to process from 2 columns are from something like A1B1 , A9B9 , A12B12 , A25B25 going to something like A10000B10000 ( without a clear pattern that i have content every 10th cell , it's random ) . That's why i needed the results to be one below another in same column ( say from C1 to C100 ) If a macro can be done , many thanks in advance -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - searches for key word in a column and concatenates ent
Many thanks !
"Dave Peterson" wrote: I didn't notice you wanted it contiguous cells. I'd add a filter and show the non-blanks, but if that doesn't help: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim DestCell As Range Dim myWord As String myWord = "house" With Worksheets("Sheet1") Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) Set DestCell = .Range("C1") End With For Each myCell In myRng.Cells With myCell If LCase(.Value) = LCase(myWord) Then DestCell.Value = .Value & .Offset(0, 1).Value Set DestCell = DestCell.Offset(1, 0) End If End With Next myCell End Sub andrei wrote: Thanks guys . Both functions work . I will try to manage with them . I prefered a macro that moves the concatenated results in a column as i explained in first post for the following reason : The cells i have to process from 2 columns are from something like A1B1 , A9B9 , A12B12 , A25B25 going to something like A10000B10000 ( without a clear pattern that i have content every 10th cell , it's random ) . That's why i needed the results to be one below another in same column ( say from C1 to C100 ) If a macro can be done , many thanks in advance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alphabetize entire rows by first word of first column | Excel Discussion (Misc queries) | |||
Need macro that concatenates cells with text | Excel Programming | |||
change first letter of a word for an entire column | Excel Discussion (Misc queries) | |||
How can you write a formula that searches for a particular word? | Excel Programming | |||
Getting valid web searches and avoiding sites that contaminate web searches | New Users to Excel |