IF statements and moving data to different columns

R

Rhythm

Hi there,

This is my first post. Hope I am in the correct place.

I have a worksheet of user data. I have username and deposit fields.
made 2 new fields called "Deposit 2", "Deposit 3".

So this is basically an output of all my users deposits. So if someon
deposited 3 times, it will say:

Rhythm 50.00
Rhythm 30.00
Rhythm 100.00

I want to do an IF statement saying: "If Rhythm appears more than once
move his 2nd deposit value (50.00) into Deposit 2 field (in the Rhyth
row!) and move 3rd deposit into Deposit 3 (again in the Rhythm row). S
the end result is that some people will have only Deposit 1 populated
while others will have all 3.

Some users have 1 deposit, some have 3. So....I'm a bit of a n00b an
if anyone can help that would appreciated.

Hope I explained myself properly. Feel free to email me directly.

Regards,

Rhyth
 
M

Max

One play to try ..

Sample construct at: http://cjoint.com/?lkpcip1ZKq
Rearranging_Data_Rhythm_wks.xls

In Sheet1,
Source data is in cols A and B,
Names in col A, deposits in col B, from row2 down

Using 2 empty cols C & D
Put in C2: =IF(A2="","",COUNTIF($A$2:A2,A2))
Put in D2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
(leave C1:D1 empty)

Select C2:D2, copy down to say, D20,
to cover the max expected extent of source data

In Sheet2,
Put the numbers 1,2,3 in B1:D1
Select B1:D1, and format as Custom, Type: "Deposit - "0""

This displays the labels in B1:D1
Deposit - 1, Deposit - 2, Deposit - 3
while retaining the underlying numbers 1,2,3 which will be read by the
ensuing formulae to extract the deposit amounts

Put in A2:
=IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

Copy A2 down to A20
(Cover the same range size as done in col D in Sheet1)

Col A returns the unique names from col A in Sheet1,
all neatly bunched at the top

Put in the formula bar for B2,
array-enter the formula by pressing CTRL+SHIFT+ENTER:

=IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$C$2:$C$20=B$1),0)),"",I
NDEX(Sheet1!$B$2:$B$20,MATCH(1,(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$C$2:$C$20=B$
1),0)))

Copy B2 across to D2, fill down to D20 to populate the grid

Sheet2 returns the desired results
 
Top