Help with Matching

  • Thread starter Thread starter tsf929
  • Start date Start date
T

tsf929

Does anyone know if the following is possible?

Example:

A B C

1 100 Invoice blank
2 500 Invoice blank
3 blank Payment 100
4 blank Payment 500
5 700 Invoice blank


I am trying to set it up so that if a number in column A matches a
number in Column B, it will return a strikethrough, color change or
some kind of notification that there was a match. I want to be able to
look at a whole page of these and be able to quicky see which are still
outstanding and which have been paid. If anyone has any ideas I would
appreciate it.

Thanks,

Travis
 
Hi

You can do this with Conditional Formatting. Select C1 and go to Format /
Conditional Formatting. Select 'Formula Is' in the first box and in the
second box put:

=COUNTIF($A$1:$A$5,C1)>0

Select the new cell format you require from the second two tabs. You can
then paint this format onto the other C cells using Format Painter.
Just a thought - you might be better off using a helper column if you want
to sort or total the affected cells.

Andy.
 
tsf929 said:
Does anyone know if the following is possible?

Example:

A B C

1 100 Invoice blank
2 500 Invoice blank
3 blank Payment 100
4 blank Payment 500
5 700 Invoice blank


I am trying to set it up so that if a number in column A matches a
number in Column B, it will return a strikethrough, color change or
some kind of notification that there was a match. I want to be able to
look at a whole page of these and be able to quicky see which are still
outstanding and which have been paid. If anyone has any ideas I would
appreciate it.

Thanks,

Travis
Click to expand...

Do you mean: "if a number in column A matches a number in column C"? You can
do this with conditional formatting.
Select the cells in column A (say A1:A100).
Format > Conditional Formatting
Change the first box to "Formula Is".
In the second box put this formula:
=(ISNUMBER(MATCH(A1,$C$1:$C$100,0)))
Press Format, choose a formatand press OK twice.

However, beware that this only indicates if a number appears in both
columns. If you have a particular number twice in column A but only once in
column B, both numbers in column A will match and therefore be indicated,
which may not be what you need. Of course, without including more
information, there is no way to differentiate between these two numbers. I
think, for your application, you would need something more, such as an
invoice number, in which case you would look for matches on this number.
 
Back
Top