IF statements and moving data to different columns  
Author Message
infobound





PostPosted: Thu Nov 10 08:04:13 CST 2005 Top

worksheet functions >> IF statements and moving data to different columns

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

--
Rhyth
-----------------------------------------------------------------------
Rhythm's Profile: http://www.hide-link.com/ ;userid=2870
View this thread: http://www.hide-link.com/

Excel294  
 
 
Max





PostPosted: Thu Nov 10 08:04:13 CST 2005 Top

worksheet functions >> IF statements and moving data to different columns 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


>
> 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. I
> made 2 new fields called "Deposit 2", "Deposit 3".
>
> So this is basically an output of all my users deposits. So if someone
> 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 Rhythm
> row!) and move 3rd deposit into Deposit 3 (again in the Rhythm row). So
> 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 and
> if anyone can help that would appreciated.
>
> Hope I explained myself properly. Feel free to email me directly.
>
> Regards,
>
> Rhythm
>
>
> --
> Rhythm
> ------------------------------------------------------------------------
> Rhythm's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28705
> View this thread: http://www.excelforum.com/showthread.php?threadid=483877
>