Tania has attended:
Excel Intermediate course
CAN I USE IF FUNCTION OR V LOOK UP
Date Details Amount Purch paypoint VAT
20.03.11 Paypoint 500.25 500.25
26.03.11 VAT 100.64 100.64
29.03.11 Paypoint 128.97
31.03.11 Menzies Dis 687.25
01.04.11 Costco 458.25
05.04.11 Bestway 128.54 128.54
06.04.11 Bookers 1090.19 1090.19
07.04.11 ADT 120.15
08.04.11 Paypoint 1102.59
09.04.11 Bank chg 69.75
10.04.11 Paypoint 1500.15
Can i use if function to distribute the above table, i have all this list of items from the bank statement whihc needs to be grouped to relevant columns accordingly. Example all the paypoint amounts needs to be under the 'paypoint' Column and the 'VAT' paid, the amounts needs to be in the VAT column. i hope i am clear, i have completed the first two rows so that it is clear. i hope i am more clear in what i want to do. please let me know how this can be achived with 'If' funciton or Vlookup. thank you for your assistance. Tania
RE: can i use if function to distribute the following table, i h
Hello Tanya,
I'm sorry we need more detailed information as to what it is you are trying to achieve. PLease provide further info or send a sample copy of the sheet for us to view.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
RE: CAN I USE IF FUNCTION OR V LOOK UP
Hi Tania
Try this (it's a bit long winded, but I think it does what you want):
On a blank worksheet, you'll need to use columns A to I:
Put the Column headers in row 1:
Cell A1: Original Data
Cell B1: Put character after the text
Cell C1: Remove duplicate numbers
Cell D1: Date
Cell E1: Details
Cell F1: Amount
Cell G1: Purch
Cell H1: Paypoint
Cell I1: VAT
Paste your data from the bank statement down column A, starting at Cell A2
Put these formulae into the following cells and copy them down their columns:
Cell B2: =TRIM(SUBSTITUTE(REPLACE(A2,1,9,"")," ","~",(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,"")," ","")))-(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,""),".","")))+1))
Cell C2: =IF(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,""),".",""))=1,B2,SUBSTITUTE(B2,RIGHT(B2,(LEN(B2)-FIND(" ",B2,1)+1)),"",1))
Cell D2: =DATEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,REPLACE(A2,1,9,""),"")),".","/"))
Cell F2: =VALUE(RIGHT(C2,(LEN(C2)-FIND("~",C2))))
Cell G2: =IF(AND(LEFT(C2,FIND("~",C2)-1)<>$H$1,LEFT(C2,FIND("~",C2)-1)<>$I$1)=TRUE,LEFT(C2,FIND("~",C2)-1),"")
Cell H2: =IF(LEFT($B2,LEN(H$1))=H$1,H$1,"")
Cell I2: =IF(LEFT($B2,LEN(I$1))=I$1,I$1,"")
You might want to hide columns B and C once you've put the formulae in.
Good luck
Kathy
RE: CAN I USE IF FUNCTION OR V LOOK UP
Hello Kathy,
I guess you got the code you sent to work.
If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
