How to find the value of a Duplicate (Name) entry?

[Eldraad]

Unlike most people with Duplicate entry problems I do NOT want to delet
it!

What I have is a report that is sent to us every day as a text file.
have it converted automatically to an excel worksheet so excel can pul
the numbers out that we need for reports.

The problem is this:

Jason Longhaul LA...................822

Jason Longhaul LA...................73

The number of rows between Jason Longhaul LA and the duplicate can an
does vary, otherwise I could find the first one and count down x numbe
of rows to get the value of the second one.

What I need to do is run my formula to get the value of the FIRST Jaso
Longhaul LA (no problem) and THEN get the value from the SECOND Jaso
Longhaul LA!

The formula I am currently using looks like:

=OFFSET('3rd MCU Manifest'!$A$1, MATCH("Jason Longhaul LA", '3rd MC
Manifest'!$B$2:$B$1900, 0),3)

the report it is pulling the numbers from is called the 3rd Mc
Manifest.

IS there a way to get the value from the first Jason Longhaul LA (lik
I am doing) and by doing something simular formula wise get the valu
of the SECOND Jason Longhaul LA?

Many thanks for ANY help (yes, I have hit cperson.com or however the
were spelled).

Eldraa

 

[Biff]

Hi!

Jason Longhaul LA...................822
Jason Longhaul LA...................73

Are those individual strings all in one cell? eg:

A20 = Jason Longhaul LA...................822
A44 = Jason Longhaul LA...................73

Biff

 

[Ron Rosenfeld]

IS there a way to get the value from the first Jason Longhaul LA (like
I am doing) and by doing something simular formula wise get the value
of the SECOND Jason Longhaul LA?

Many thanks for ANY help (yes, I have hit cperson.com or however they
were spelled).

It is possible to get all of the Jason's.

You will have to modify the following for your setup.

I have assumed that the Names are in a named range called "Names" and the
values you want to pull are in a named range called Values.

I'm not sure how you are going to set up your output table, but the following
formula assumes that the name you are looking for is in F1, and that the output
will start in Row 1 of some column.

Highlight enough of the column to more than cover all instances of any name
(e.g. maybe 10 cells?). Then copy/paste or type the formula into the first
cell and, with the selection still highlighted, hold down <ctrl><shift> while
hitting <enter>. This will enter an *array* formula into those cells and bring
up the Jason values (in reverse order).

=IF(ROW()>COUNTIF(Name,$F$1),"",INDEX(Value,LARGE(($F$1=Name)*
ROW(INDIRECT("1:"&COUNTA(Name))),
ROW(INDIRECT("1:"&COUNTIF(Name,$F$1))))))

However, if you can sort your data, you could Sort on the name (Data/Sort) and
then use the Data/Subtotals wizard if necessary.

Finally, you could use a Pivot Table. If you put both Name and Values in the
"Column" area, and Values in the Data area, you can see both the individual and
the totals for each name. This would not require sorting the original data,
but would require refreshing the Pivot Table when changes were made in the
original data.




--ron

 

[Eldraad]

Thank you for your replies Ron and Biff...


Sorry I wasn't as clear as I could have been.

The name and the values are in different cells (both name listings ar
in column A and the values in column D with the vales in the same row
as the names, yes the names are in two differnt rows).

The position or where the names occur in the list changes greatly.

I do not belive we can go through the highlighting and selecting
range as people doing this will have next to no knowledge of exce
(like me).

So, unless there is a way to find the FIRST instance of Jason Longhau
LA, get its value and then look for the next instance (only two occur
then we are out of luck with the automation of the report. They wante
it so it opens, we tell it where the reports are and it generates th
values that we send to the field

 

[Ron Rosenfeld]

Thank you for your replies Ron and Biff...


Sorry I wasn't as clear as I could have been.

The name and the values are in different cells (both name listings are
in column A and the values in column D with the vales in the same rows
as the names, yes the names are in two differnt rows).

The position or where the names occur in the list changes greatly.

I do not belive we can go through the highlighting and selecting a
range as people doing this will have next to no knowledge of excel
(like me).

So, unless there is a way to find the FIRST instance of Jason Longhaul
LA, get its value and then look for the next instance (only two occur)
then we are out of luck with the automation of the report. They wanted
it so it opens, we tell it where the reports are and it generates the
values that we send to the field.

What happened when you tried each of my three suggestions. I will not be able
to help without that information.


--ron