IF statements and moving data to different columns |
|
Author |
Message |
infobound
|
Posted: 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
|
Posted: 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
>
|
|
|
|
|
|
|