Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup all values within multiple columns and copy to new column
I have a list of strings in column A and column J. Because the number
in each column is variable, the whole column should be referenced. I would like to lookup all instances of a string that is in column S and then have each string found output to column W. There may be e.g. 10 strings that match the string I want, i therefore want 10 strings output into W. How do I go about this? Thanks, Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup all values within multiple columns and copy to new column
Sub copycols()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A")) LastRow = Cells(Rows.Count, "J").End(xlUp).Row Set ColJRange = Range(Cells(1, "J"), Cells(LastRow, "J")) LastRow = Cells(Rows.Count, "S").End(xlUp).Row Set ColSRange = Range(Cells(1, "S"), Cells(LastRow, "S")) 'get last row in column W LastRow = Cells(Rows.Count, "W").End(xlUp).Row If (LastRow = 1) And IsEmpty(Cells(1, "W")) Then RowCount = 1 Else RowCount = LastRow + 1 End If For Each cell In ColSRange Set FoundinA = ColARange.Find(what:=cell, LookIn:=xlValues) Set FoundinJ = ColJRange.Find(what:=cell, LookIn:=xlValues) If (Not FoundinA Is Nothing) Or (Not FoundinJ Is Nothing) Then Cells(RowCount, "W") = cell RowCount = RowCount + 1 End If Next cell RowCount = 1 End Sub "Tommy" wrote: I have a list of strings in column A and column J. Because the number in each column is variable, the whole column should be referenced. I would like to lookup all instances of a string that is in column S and then have each string found output to column W. There may be e.g. 10 strings that match the string I want, i therefore want 10 strings output into W. How do I go about this? Thanks, Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup all values within multiple columns and copy to new column
a nice piece of code, but it does not do what I need. Your code finds
all unique tags within the defined range. I require an output of all identical tags within a range. say for example there are 10 cells in a single column range and 4 of them contain the same tag, "06TI1845.PV" for example. Well I need the output in column W to list 06TI1845.PV 4 times..... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup all values within multiple columns and copy to new colu
I think this is what you really wanted. Original request wasn't specific
enough. Sorry Sub copycols() LastRow = Cells(Rows.Count, "A").End(xlUp).Row Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A")) LastRow = Cells(Rows.Count, "J").End(xlUp).Row Set ColJRange = Range(Cells(1, "J"), Cells(LastRow, "J")) LastRow = Cells(Rows.Count, "S").End(xlUp).Row Set ColSRange = Range(Cells(1, "S"), Cells(LastRow, "S")) 'get last row in column W LastRow = Cells(Rows.Count, "W").End(xlUp).Row If (LastRow = 1) And IsEmpty(Cells(1, "W")) Then RowCount = 1 Else RowCount = LastRow + 1 End If For Each Cell In ColSRange For Each CellA In ColARange If Cell.Value = CellA.Value Then Cells(RowCount, "W") = Cell.Value RowCount = RowCount + 1 End If Next CellA For Each CellJ In ColJRange If Cell.Value = CellJ.Value Then Cells(RowCount, "W") = Cell.Value RowCount = RowCount + 1 End If Next CellJ Next Cell End Sub "Tommy" wrote: a nice piece of code, but it does not do what I need. Your code finds all unique tags within the defined range. I require an output of all identical tags within a range. say for example there are 10 cells in a single column range and 4 of them contain the same tag, "06TI1845.PV" for example. Well I need the output in column W to list 06TI1845.PV 4 times..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Multiple Columns, Paste Under a Single column | Excel Worksheet Functions | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
can vlookup be used to if the lookup values are in two columns? | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Copy columns values into separate columns | Excel Discussion (Misc queries) |