Find multiple values

[atroul]

Hello,
I have a workbook with multiple sheets. In the first sheet there are (among others) some values (~500) which I need to search if they exist and where in the other sheets.
In a few words I need the "Find" function for multiple values.

 

[My Aswer Is This]

Ok. We need:
1. The name of the sheet with all the values in a list.
2. The column number where this list of values are.
3. What sheets will we be searching for these values.
4. What range in each sheet will we be searching for these values
5. Will we be looking for the exact value or a exact value within a string.
6. What will we do when we do find the value.

Please answer each question and maybe we can help you.

 

[atroul]

1. The name of the sheet with all the values in a list.

Name of workbook: 1A
Name of sheets: 1a, 1b, 2a, 2b, ..... , 11a, 11b

2. The column number where this list of values are.

Column letter: C

3. What sheets will we be searching for these values.

All sheets

4. What range in each sheet will we be searching for these values

From row 23 and below (23->200 or more its different from sheet to sheet)

5. Will we be looking for the exact value or a exact value within a string.

We will search for each value in these cells in all sheets if they exist in the whole workbook. ( sample data : "P08670" )

6. What will we do when we do find the value.

Return the sheet(s) number where they ve been found

Thank you :D

 

[My Aswer Is This]

You said in your original post:
"In the first sheet there are (among others) some values (~500"

I assumed the list of values we will be looking for are only in the "First sheet" I asked what is the name of the first sheet and you said: Name of sheets: 1a, 1b, 2a, 2b, ..... , 11a, 11b

That seems to mean we have more then one and those seem to be some odd names for sheets.

But then you said the workbook name was 1A which also seems like a odd name

And I asked what do we do when we find one of these names in the list and you said:
Return the sheet(s) number where they have been found
Return the sheets(s) number where?

 

[atroul]

every sheet in the workbook has a column (C) where there are some values.
For example:
sheet 1a has values in the range C:23 .... C:180
sheet 1b has values in the range C:23 .... C:200
etc.
I need the program ( or function ) to print something like this:
"P08670 found in sheets: 2b, 10a"
"O09738 found in sheets: 5a"
etc.

 

[Rick Rothstein]

I need the program ( or function ) to print something like this:
"P08670 found in sheets: 2b, 10a"
"O09738 found in sheets: 5a"
etc.

The values I have highlighted in red above... do they exist in list form so we can know what values to look for in Column C on the sheets in the workbook? If so, what sheet is this list on and in what range? Also, does Column C of that sheet need to be checked?

Or don't you know in advance what the values will be beforehand (meaning the code would have to figure them out)?

Where did you want the output you requested to go to?

 

[My Aswer Is This]

Ok I'm here trying to help people but at a certain point if I'm not able to understand you I will need to hope someone else here can help you.

So I will try one more time.

You want to search all worksheets in your workbook for the word "Bob" for example.
And you said search for "Bob" in column "C" of each sheet.

And now here are two question I still have not received a good answer for.

1. Where will I get the word "Bob" from? It I need to look for "Bob" where was "Bob"
2. And I have asked twice now what do we do when we find the word "Bob" and your answer was:

I need the program ( or function ) to print something like this:
"P08670 found in sheets: 2b, 10a"
"O09738 found in sheets: 5a"

Printing means printing on a piece of paper. I need to know where in the workbook this is supposed to be printed. I cannot print this on the screen or on a piece of paper. I need to put this data in a cell or cells on some sheet.

 

[atroul]

The code will have to figure them out.
Algorithm:
Read C:23 of sheet 1a -> Check if that value exist in all the sheets of the workbook -> if exists return the sheets' number (return it to the console or in a new sheet, doesnt really matter I just need that info)
Read C:24 of sheet 1a ......
.......
Read C:23 of sheet 1b ......
......
......

 

[My Aswer Is This]

Glad to see you here Rick.

I will hope you can help this poster. I'm not sure how to help. We still have not been told where the list of values to search for are located at least not that I can understand. And still no absolute place to return the results unless we add a new sheet put the data in this sheet and activate it in the last line of code so the user will see the results. I'm exhausted.

 

[atroul]

I think that the algorithm I posted in my previous post make it easier to understand....
The values are in the sheets in the C column from 23 row to ~200 (the ending row its not the same in every sheet).

The result data can be in a new sheet.