Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search Columns & Rows
Hi All
Is there a macro or routine that will read a list of code numbers in col"A" of Sheet1 then search and find the same code numbers in another 10 sheets in col"A" then place the information in cols "B" & "C" of Sheet1 in the first empty col when it finds the code number in any of the other Sheets. Sheet 10 has a complete list of the same codes and I need to exclude it altogether from the original search Thanks Mully |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search Columns & Rows
Hi Mully,
Name the area of column A with data in each additional worksheet- Look1, Look2, etc€¦ include an empty cell at the top and bottom of the area. In column B use a function similar to the following: =IF((LOOKUP(A1,Look1)=A1),A1,IF((LOOKUP(A1,Look2)= A1),A1,"not found")) You would need to nest IF functions for each sheet additional sheet. A1 is the first cell in first worksheet in column A. LOOKUP will only work with IF function if you populate the cell at the top of the area with a negative value that will never be reached and the cell at the bottom of the area with a value that will never be reached. And, you have to sort each named area, ascending order. Hope that helps. PB "mully" wrote: Hi All Is there a macro or routine that will read a list of code numbers in col"A" of Sheet1 then search and find the same code numbers in another 10 sheets in col"A" then place the information in cols "B" & "C" of Sheet1 in the first empty col when it finds the code number in any of the other Sheets. Sheet 10 has a complete list of the same codes and I need to exclude it altogether from the original search Thanks Mully |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
How can I transpose rows to columns in a large worksheet? | Excel Discussion (Misc queries) | |||
REQ: Columns to rows or Rows to Columns | Excel Discussion (Misc queries) | |||
How can i convert the rows into columns and columns into rows? | Excel Worksheet Functions | |||
Convert multiple columns to rows | Excel Worksheet Functions |