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?
--
EOD- we take the licking when it stops ticking.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave" wrote:
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?
--
EOD- we take the licking when it stops ticking.

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.

--
EOD- we take the licking when it stops ticking.


"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave" wrote:
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?
--
EOD- we take the licking when it stops ticking.

"Dave" wrote:
Great idea and I love the way it works,
will probably use that for another project.

Glad you can see the cross applications for that little number elsewhere <g.
Even here, the set-up done can be further used for what you want below ..

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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the help, alot of great ideas...

--
EOD- we take the licking when it stops ticking.

welcome, Dave.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave" wrote in message
...
Thanks for the help, a lot of great ideas...