Finding Data from 4 sheets and 4 columns....

[jaypatel]

Hi,

On one spreadsheet i have 4 worksheets which are 4 separate competencies......Sheet 1 through to 4.

In column B of all the 4 sheets are company names.....

so sheet 1, B2-b250 is company names
sheet 2, b2-1900 is company names etc

Is there a way to find out which companys are in each of the 4 sheets within column B? For example, in sheet 1 B25 and sheet 2 B67 and sheet 3 B890 and sheet 4 B567, there could be the name of Jay Patel Ltd, and for the result(s) to be inserted in Sheet 5? All i need is the company names.

Appreciate any help you can give me.

Regards

Jay

 

[hatman]

Well, a quick and dirty (and unformatted) solution might be

=IF(AND(COUNTIF(Sheet2!B:B,Sheet1!B12)>0,COUNTIF(Sheet3!B:B,Sheet1!B12)>0,COUNTIF(Sheet4!B:B,Sheet1!B12)>0),Sheet1!B12,"")

 

[jaypatel]

Hi,

Just tried it and it does not work.....

I think the problem could be a text issue.

Also like to point out that text could be on different rows eg row 57 in sheet 1, row 90 in sheet 2 etc.

Regards

Jay

 

[hatman]

Hi,

Just tried it and it does not work.....

You should have gotten at least a FEW matches. You DID copy the formula down to correspond with the number of rows on Sheet 1, right?

I think the problem could be a text issue.

What do you mean by a text issue? Do you mean that names are spelled differently on each sheet, but they need to match? Are there extra spaces? Is there additional text in some entries? Depending upon the variation, there may be ways around it... can you post a sample of data that is suposed to match but doesn't seem to?

Also like to point out that text could be on different rows eg row 57 in sheet 1, row 90 in sheet 2 etc.

Yup, that's the assumption that I made... this formula does not care whether the names are on different lines or not (it would be pretty silly to assume that they might be :wink: ).

The reason that I called this solution "Dirty" is because the output ends up in exactly the same order as the entries on sheet1, with blank cells where the data does not match... so you end up with alist that has holes in it. This can be scrunched up, if you need it, but I figured I'd take this a step at a time...