comparing cell to row, return all matchs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

AHello, working on a project but can't get formula to work, started with
nested IF but due to limitations gave up.

Heres the problem...
When working with the below route line (imported from AS400) what formula
string can I use to bring back any result of duplication?
A B C D E F G H I J
K L M
KENNWA E PASCWA E KENNWA E YAKIWA E ONTAOR E FONTCA L YAKIMA

Not only do I want to know that KENNWA is in the row 2 times.. but YAKIMA is
as well. (regardless of E or L between them)

currently seem to have some success with the following...

=IF(COUNTIF($A1:M13,A1)>1,1,0)

Did the >1 due to it finding itself every time... but having to replicate
formula for each cell thru M.

Is there a faster/easier way?
 
One possible quick play to try, where source data is extracted into a single
col on another sheet, with a pivot table then applied to retrieve the unique
listing of items and their corresponding counts ..

Assume source data in cols A to M in sheet: x, data from row1 down

In another sheet,
Type a header in A1, eg: Head1

Put in A2:
=OFFSET(x!$A$1,INT((ROWS($1:1)-1)/13),MOD(ROWS($1:1)-1,13))
Copy A2 down as far as required, until zeros appear signalling exhaustion of
data. This extracts source data in x's cols A to M (13 cols) into a single
col.

Then create a pivot table on the extracted data in col A:
Select col A, click Data > Pivot table. Click Next > Next. In step 3, click
Layout, then drag n drop "Head1" into the ROW area, and into the DATA area.
Click OK > Finish. That's it. Hop over to the PT sheet for the results.
 
Great idea and I love the way it works, will probably use that for another
project.
As for this one I still need help.

9000 rows of route lines.
I need formula string to filter thru them and mark specific rows that have a
duplicate city within that row, regardless of where the duplicate city
appears.
 
Dave said:
Great idea and I love the way it works,
will probably use that for another project.

As for this one I still need help. 9000 rows of route lines.
I need formula string to filter thru them and mark specific rows that have a
duplicate city within that row, regardless of where the duplicate city
appears.
Click to expand...

Extending the earlier set-up ..

In the PT sheet,

Do a quick copy n paste special as values, & clean up of the unique
destinations (remove "E", "L" and zeros) for Head1's items somewhere on the
sheet, eg:

FONTCA
KENNWA
ONTAOR
PASCWA
YAKIMA
YAKIWA
etc

Then define a named range: Destn
to refer to that list

Then over in the source sheet: x,

Place this in N1, and array-enter the formula (press CTRL+SHIFT+ENTER to
confirm the formula):
=IF(MAX(COUNTIF(A1:M1,Destn))>1,"Duplicate","")
Copy N1 down to cover the 9,000 rows. Col N will flag the required lines,
that have a duplicate city within that row, regardless of where the duplicate
city appears.

---
 
Back
Top