Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mully
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Pancake Batter
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rows & Columns in Excel seadragon69 Excel Worksheet Functions 2 December 7th 05 06:54 PM
How can I transpose rows to columns in a large worksheet? ratchick Excel Discussion (Misc queries) 7 November 11th 05 05:25 PM
REQ: Columns to rows or Rows to Columns nonapp Excel Discussion (Misc queries) 4 October 26th 05 12:24 AM
How can i convert the rows into columns and columns into rows? Yasser Excel Worksheet Functions 1 August 7th 05 07:38 AM
Convert multiple columns to rows Lois Lane Excel Worksheet Functions 8 January 10th 05 01:47 AM


All times are GMT +1. The time now is 08:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"