Excell COUNTIF

[mtevensorrison]

Hi there

I'm need of a little advice from one of you geniuses out there!

Here goes

I use the followinf formulae --

=(COUNTIF(remitt!A:A,A7908)>0)+0

This formulae shows me which numbers in sheet2 collumb 1 are actually in sheet 1 collumb 1 - -hope that makes sense!

The formula is placed in sheet 1 collumb 2, and is is shown as either a 1 or a 0.

Stay with me

Now heres what im after, this method compares collumb 1 in different worksheets!

Would i be able to compare collumbs 1 + 2 in each work sheet!

Thanks in advance

 

[AnotherHiggins]

Hi there.

First, why is the "+0" at the end of your formula there? (=(COUNTIF(remitt!A:A,A7908)>0)[red]+0[/red] ) Countif already returns a number - adding zero won't change anything.

As for your question, I need clarification:

1) do you want to see if the value in sheet 1 column 1 are in either columns 1 or 2 of sheet 2
[tab]or
2) do you want to find instances where columns 1 and 2 or sheet 1 are identical to columns 1 and 2 of sheet 2

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

Hi there

Thanks for the reply

I don't really know why that 0 is on there!

Its option 2 i would be looking for please.

Thanks again

Steven

 

[AnotherHiggins]

The simplest thing that comes to mind is to use a "helper column" on each sheet. These columns can be hidden if you wish.

Let's say your data is in columns A & B. In C2 (I'm assuming you have a header row) you could put
[COLOR=blue white]=A2&B2[/color]
and copy or fill down. Do that on both sheets.

Now use
[COLOR=blue white]=COUNTIF(remitt!C:C,C2)[/color]

I'm sure that isn't the most elegant solution, but given your starting point it's the easiest to understand.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

Would that mean inserting the formulae in to collumn D?

Cheers

 

[AnotherHiggins]

Yep.

Or your hidden column can be D and your formula can be in C. Whatever works for you.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

Sorry to be a pest but this helper collumn is just going to keep the sum off A2 + B2 added together!

Thanks

 

[AnotherHiggins]

NO!!!

It isn't adding them together, it is concatenating them. Huge difference.

If, on a given row, column A contains "2" and column B contains "2" then the helper column contains "22", not "4".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

Ok im with you now

That is exactly what im looking for

Is their a specific way of setting up a helper coolumn?

Cheers

 

[AnotherHiggins]

All I meant was that I used A2 and B2 in the examples (instead of A1 and B1) because you might have header names in row 1.

If you don't, you can just use the formula in row 1, too.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

Yeah im with you about the headers!

so all i put in C2 or(without headers) is =A2&B2

Will this be on both workbooks?

then in D2 i will put =COUNTIF(remitt!C:C,C2)

Thanks

 

[AnotherHiggins]

Yes - on both workbooks.

Give it a try and see how it works.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mtevensorrison]

OK i've got it going

So its now comparing collumn C on both worksheets and letting me know if that value in workshhet 1 exists in worksheet 2 and also if theres multiple entry's of the same value.

Confusing stuff this, especially when you working with 15,000 rows of data

Thanks