A complecated Excel problem.

  • Thread starter Thread starter matrix7410
  • Start date Start date
M

matrix7410

Is there a way to identify all the names that have one consumer ID but
has different System IDs. My output based on the below example should
be Michael Amber as it has one kind of Consumer ID but have two
different System IDs. thanks!
System ID: Name: Consumer ID:
002744 Michael Amber 0409550
002744 Michael Amber 0409550
002743 Michael Amber 0409550
002743 Michael Amber 0409550
001481 John Smith 0226114
001481 John Smith 0226114
 
You can use an advanced filter, assume the table is in A8:C14 with the data
starting in row 9
in G2 put

=SUMPRODUCT(--($B$9:$B$14=B9),--($C$9:$C$14=C9))<>SUMPRODUCT(--($A$9:$A$14=A9),--($B$9:$B$14=B9),--($C$9:$C$14=C9))


Select A8:C14, do data>filter>advanced filter (then you should have the list
range in place), in the criteria box put

$G$1:$G$2


select copy to another location and select where you want it, then select
unique records only


that will give you using your sample


System ID: Name: Consumer ID:
002744 Michael Amber 0409550
002743 Michael Amber 0409550




--


Regards,


Peo Sjoblom
 
Is there a way to identify all the names that have one consumer ID but
has different System IDs. My output based on the below example should
be Michael Amber as it has one kind of Consumer ID but have two
different System IDs. thanks!
System ID: Name: Consumer ID:
002744 Michael Amber 0409550
002744 Michael Amber 0409550
002743 Michael Amber 0409550
002743 Michael Amber 0409550
001481 John Smith 0226114
001481 John Smith 0226114
Click to expand...

Is Consumer ID always the same for each name?
I.e., are these entries allowed:

002744 Micael Amber 0409551
002745 Micael Amber 0409551

???

Bruno
 
Here is another way:

Assuming that the data is sorted by Consumer Id and System Id and is in
columns a:C starting in row 2

Add in cell D2
=IF(and(C2=C1,a2<>a1),1,0)
drag/autofill this formula down

then filter the list for 1 in column D

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
It's easy peasy with a Pivot table.

Select all the data.
(Or just place the cursor in the data list.)

Go to Excel Menu...
Click on Data.
Pivot table..
Excel table ..
Confirm the selected data....

Layout is
for the "Rows" :
Consumer ID
Name
System ID


For the "Columns"
nothing (=leave it empty)

In the "data" area
Name (Count of )


OK, OK, OK..

Now right-click on one of the Customer IDs.
Field settings..
Advanced
Auto sort
Reverse sort on Count of names.


Do you want me to mail you back your table ?
Or do the whole thing it for you ?

R.


Numbre de Name:
Consumer ID: Name: System ID: Total
409550 Michael Amber 002743 2
002744 2
0226114 John Smith 001481 2
Total 6
 
I really don't see how this answers the post? The OP asked to identify the
name that had
one consumer ID but had different system IDs.


--


Regards,


Peo Sjoblom
 
You can use an advanced filter, assume the table is in A8:C14 with the data
starting in row 9
in G2 put

=SUMPRODUCT(--($B$9:$B$14=B9),--($C$9:$C$14=C9))<>SUMPRODUCT(--($A$9:$A$14=­A9),--($B$9:$B$14=B9),--($C$9:$C$14=C9))

Select A8:C14, do data>filter>advanced filter (then you should have the list
range in place), in the criteria box put

$G$1:$G$2

select copy to another location and select where you want it, then select
unique records only

that will give you using your sample

System ID:           Name:              Consumer ID:
002744      Michael Amber             0409550
002743      Michael Amber             0409550

--

Regards,

Peo Sjoblom






- Show quoted text -
Click to expand...

Thanks! Peo, your sumproduct formula did the trick. Thanks again.
 
Back
Top