R
Rayasiom
Hi All,
I receive a client data spreadsheet from my supplier that they can't change
so I need to change it to an easier format:
A B C D
1 01-Apr-07 useless data amount
2 29-Apr-07 amount
3 MURJ-001 Subtotal
4 15-Apr-07 useless data amount
5 22-Apr-07 amount
6 MURC-001 Subtotal
and so on.....
Data in columns B and D are not required.
I need to get the data into the format of:
G H I
1 Apr-07 MURJ-001 Amount
2 Apr-07 MURJ-001 Amount
3
4 Apr-07 MURC-001 Amount
5 Apr-07 MURC-001 Amount
6
In Cell F1 I have =IF(ISNUMBER(A1),1,0) to give the value 1 if there is a date
In G1 I have =IF(F1=1,A1,0) formatted to mmm-yy
In I1 I have =IF(F1=1,C1,0)
My problem is in H1. I have:
=IF(F1=0,A1,IF(F2=0,A2,IF(F3=0,A3,IF(F4=0,A4,IF(F5=0,A5,IF(F6=0,A6,IF(F7=0,A7,IF(F8=0,A8,0))))))))
which limits me to 7 date and amount entries with the 8th row having the
client code.
As it is I get the following using the above:
F G H I
1 Apr-07 MURC-001 1,080.00
1 Apr-07 MURC-001 1,350.00
0 MURC-001 0.00
1 Apr-07 MURJ-001 1,181.25
1 Apr-07 MURJ-001 1,181.25
0 MURJ-001 0.00
Thanks.
I receive a client data spreadsheet from my supplier that they can't change
so I need to change it to an easier format:
A B C D
1 01-Apr-07 useless data amount
2 29-Apr-07 amount
3 MURJ-001 Subtotal
4 15-Apr-07 useless data amount
5 22-Apr-07 amount
6 MURC-001 Subtotal
and so on.....
Data in columns B and D are not required.
I need to get the data into the format of:
G H I
1 Apr-07 MURJ-001 Amount
2 Apr-07 MURJ-001 Amount
3
4 Apr-07 MURC-001 Amount
5 Apr-07 MURC-001 Amount
6
In Cell F1 I have =IF(ISNUMBER(A1),1,0) to give the value 1 if there is a date
In G1 I have =IF(F1=1,A1,0) formatted to mmm-yy
In I1 I have =IF(F1=1,C1,0)
My problem is in H1. I have:
=IF(F1=0,A1,IF(F2=0,A2,IF(F3=0,A3,IF(F4=0,A4,IF(F5=0,A5,IF(F6=0,A6,IF(F7=0,A7,IF(F8=0,A8,0))))))))
which limits me to 7 date and amount entries with the 8th row having the
client code.
As it is I get the following using the above:
F G H I
1 Apr-07 MURC-001 1,080.00
1 Apr-07 MURC-001 1,350.00
0 MURC-001 0.00
1 Apr-07 MURJ-001 1,181.25
1 Apr-07 MURJ-001 1,181.25
0 MURJ-001 0.00
Thanks.