Skip cell if X nonexistent, populate next cell

[prodeji]

OK here's my dilemma; hope I explain myself clearly as I'm new at
this.

I have a worksheet that pulls the names and IDs of casino players
enrolled in different groups from a database.


Every person in the group is assigned a player ID.


Not every person plays, however.


I'm compiling a report that shows actual and theoretical win for each
player.


Table X stores player names and IDs, table Y stores player
statistics.
I calculate actual and theoretical win from table Y, set up a pivot
table, and link 2 other columns to the pivot table to display actual
and theoretical win.


Problem is, only players who actually play have any records in table
Y.


The bosses want to see ALL persons enrolled in group X, whether they
played or not.


With my limited knowledge of Excel, I have 2 options:


A. I modify the report to show ONLY those persons in the group who
actually played.


B. I show all persons in the group, but the player name/IDs and
actual/
theoretical win figures will no longer match up.


e.g group 234 includes the following, modified to show only persons
who played:


Plyr ID Last Name First Name Theo Win Actual
Win
53082 Duck Donald $0.60 $10.00
53084 Man Super $26.30 $70.00
53087 Baby Big $1.20 $3.75
53088 Dick Moby $28.10 $83.75


Everyone's play is correctly attributed to him/her, but when I modify
the report to include persons who did NOT play, Mr. Jolly Green Giant
now appears on the list, and has Big Baby's figures incorrectly
attributed to him, Big Baby in turn gets Moby Dick's data, and so on
and so on.


Plyr ID Last Name First Name Theo Win Actual
Win
53082 Duck Donald $0.60 $10.00
53084 Man Super $26.30 $70.00
53085 Giant Jolly Green $1.20
$3.75
53087 Baby Big $28.10 $83.75
53088 Dick Moby


How can I extract actual and theoretical win from the pivot table
(which contains ONLY those players who played, along with their
stats), match it up with the corresponding player IDs and names on
the
report (which contains ALL player IDs/names whether they played or
not) and instruct Excel to skip or better yet populate with '$0.00'
those cells that belong to players who did NOT play?


As in Mr. Jolly Green Giant would now be included on the report, but
would have '$0.00' in his 'Theo Win' and 'Actual Win' columns:


Plyr ID Last Name First Name Theo Win Actual
Win
53082 Duck Donald $0.60 $10.00
53084 Man Super $26.30 $70.00
53085 Giant Jolly Green $0.00
$0.00
53087 Baby Big $1.20 $3.75
53088 Dick Moby $28.10 $83.75


Really hope I'm being clear enough about what I need, as I said, I'm
new to this, but trying to be precise and provide all necesssary
information.


Any guidance is deeply appreciated.


Thanks,

 

[Guest]

It is unclear how you are linking to addtiional columns to the pivot table,
but if you are getting the wrong results, you apparently are not doing it
correctly. Maybe you are using a vlookup and not going for an exact match?

 

[prodeji]

It is unclear how you are linking to addtiional columns to the pivot table,
but if you are getting the wrong results, you apparently are not doing it
correctly. Maybe you are using a vlookup and not going for an exact match?

--
Regards,
Tom Ogilvy

























- Show quoted text -

Apologies, I guess I was indeed not being clear.

Umm...

Basically I have a column X that has say 25 rows. Elsewhere on the
spreadsheet I have column X appearing again paired with column Y and
containing only those rows that have corresponding data in column Y.
This reducies column X to say 20 rows.

I now need to match up the values from column Y with the corresponding
values in the original column X, inserting '$0.00' or something
similar in the rows where the original column X does not have a
corresponding value in column Y.

Hope this is clearer, thanks,

 

[Guest]

Assuming the first column X values are starting in A1 and the second are
starting in M1, amounts in N1 (down to row 20), as an example,

=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1,$m$1:$N$20,2,False))

entered in B1, then drag fill down 25 rows.

 

[prodeji]

Assuming the first column X values are starting in A1 and the second are
starting in M1, amounts in N1 (down to row 20), as an example,

=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1,$m$1:$N$20,2,False))

entered in B1, then drag fill down 25 rows.

--
Regards,
Tom Ogilvy








- Show quoted text -

Hi again Tom,

I took the code you suggested and modified it to reflect cell values
on my worksheet (I'll put them side by side for comparison):

=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1,$m$1:$N$20,2,False))

=if(iserror(match($L7,$H$3:$I$300,0),0,Vlookup($L7,$H$3:$I
$300,2,False))

I don't know if I have some step/procedure done incorrectly or omitted
altogether but I got an 'The formula you typed contains an error'
message both when using my modified version and the original.

Any thoughts?

Thanks,

 

[Tom Ogilvy]

=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1,$m$1:$N$20,2,False))

doesn't close the iserror function.


=if(iserror(match($A1,$M$1:$N$20,0)),0,Vlookup($A1,$m$1:$N$20,2,False))

 

[prodeji]

=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1,$m$1:$N$20,2,False))

doesn't close the iserror function.

=if(iserror(match($A1,$M$1:$N$20,0)),0,Vlookup($A1,$m$1:$N$20,2,False))

--
Regards,
Tom Ogilvy










- Show quoted text -

WOW.
Well I feel like a COMPLETE...

Thanks guy