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

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Well, a quick and dirty (and unformatted) solution might be
Code:
=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

Active Member
Joined
Nov 25, 2002
Messages
385
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Hi,

Just tried it and it does not work.....
:unsure:
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...
 

Forum statistics

Threads
1,136,277
Messages
5,674,795
Members
419,525
Latest member
mountainkids

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top