Searching for a value in multiple sheets

[hrs4545]

Sir,

In the attached file which consists of multiple sheets, column 'K' contains chemical compound names. I have to search for the same compound name exists in this worksheet and in different worksheets (i.e., comparing value in 'K1' cell with all the cell values in that column i.e., K2....Kn). If it is found in the same worksheet or in different worksheets, it has to be highlighted. This process is to be repeated for all cell values in column 'K'. (i.e., compare value in 'K2' with all cell values in column 'K' in the same worksheet and in different work sheets). I attached a partial part of the file but it is a big file. I tried with VLOOKUP and failed to get it as I am a research scholar in chemistry.

[JBeaucaire]

I would have to recommend you employee VBA for this.

Here's a simple User Defined Function (UDF) I created to do this easily for you. The function is used in the workbook like so:

=CountInSheets(CellwithString, ColumnNumber)

For instance: =CountInSheets(K1, 11)
This tells you how many times the value in K1 appears in column 11 in all sheets in the workbook. Here's the code:

Please Login or Register  to view this content.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.
===============

Now, since you want the duplicated cells to be "highlighted", I would use this new function as a Conditional Formatting formula.

1) Highlight all the sheets.
2) Highlight column K
3) Create a conditional formatting rule in that column with the color of your choosing and the formula:
=CountInSheets(K1, 11)>1

Now, all the items that appear in more than one place will highlight the color you selected. Here's your workbook back with that installed and working.

[JBeaucaire]

Thanks for the feedback. If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[Fotis1991]

Dove si trova la risposta?


Edit: Translation: Where is the feed back?

[hrs4545]

I could not find the answer with the code as "CountInSheets(K1,11)>1"

[hrs4545]

I am unable to find the answer with the code as "CountInSheets(K1,11)>1". Help me

[JBeaucaire]

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook has the UDF I gave installed and the Conditional Formatting formulas I explained entered as well. It's your work I'd like to test.

Also, I'm changing your USERNAME to remove the @yahoo.com, so you will need to login with just hrs4545 from now on.

[hrs4545]

Respected Sir

I am herewith attached two files; one is excel sheet which consists of multiple worksheets (including macro) and other one is snapshot of requirement in jpg format. If we type the compound name like "Methyl Tetradecanoate" ( as shown in snapshot), it has to search in column 'K' of all worksheets, if a match is found, those rows will be copied into a new worksheet. Then I will give next compound name and repeat this for one by one. PLS DO THE HELP.

[JBeaucaire]

You installed the new function into a module. Good job.

Then you did nothing with it. Follow these SECOND set of instructions taken from post #2. This applies conditional formatting to column K of all your sheets to accomplish your desired results.

Now, since you want the duplicated cells to be "highlighted", I would use this new function as a Conditional Formatting formula.

1) Highlight all the sheets.
2) Highlight column K
3) Create a conditional formatting rule in that column with the color of your choosing and the formula:
=CountInSheets(K1, 11)>1

Now, all the items that appear in more than one place will highlight the color you selected. Here's your workbook back with that installed and working.

You might have to do these steps on one sheet at a time.

[JBeaucaire]

Here's a macro that will add the conditional formatting for you:

Please Login or Register  to view this content.

It does require the original function given, too, so both will need to be in the workbook.

[hrs4545]

Thanks for your reply sir. In that file, all duplicate values are highlighted in multiple work sheets and the count also displayed sir. Actually, I need the result as "those duplicate rows (based on value of K) from all sheets has to be copied into another sheet (in order) sir. For example "Propanoicacid, decyl ester" showed the count as '3' times. These three rows from multiple sheets will be copied into another work sheet (Say 'result.xls'). Similarly next compound "ISOCHIAPIN B" counted as 8. These eight rows has to be copied / appended after 3 rows of the file result.xls. The last column of each row of 'result.xls' is the name of the excel sheet(s) where these compound name was found. Similarly for all duplicate cells sir. Because I am new to this, PLS KINDLY HELP ME SIR.

[JBeaucaire]

It's crazy. Just copy all the data from all sheets into a single worksheet. Then sort the list by column K to get all like rows together. Then flag every row that has no duplicates in column K and delete them.

Run this macro with your data workbook onscreen. Be sure to edit the PATH where you want the new file saved:

Please Login or Register  to view this content.

[hrs4545]

I attached four excel sheets. In the first workbook 'FAME.xls' contains 3 worksheets names 1,2,3 and a macro to combine them into a new worksheet named 'conslidated'. I copied that worksheet into a new file and named it as 'Book2'(that contains a single file contains all records of worksheet 1,2,3). I executed the macro 'duplicates'(given by you) and the result file is 'result.xls'. In that file, I am not getting the source address of each row i.e., where it is originally placed. The actual output required is shown in 'result-required.xls'. The last column 'L' indicates the file name followed by row position. 2-2 means file name 2, row is 2. Similarly all four row positions are displayed. I tried a lot but failed to get it. Pls help me sir