Alternative to using IF function to extract data

[Guest]

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.

 

[Dave Peterson]

I did this and it seemed to work ok.

First, I copied your sample data into A16--you'll be adjust row 6 to match
your last row of data.

Then I deleted columns B and D

Then I inserted a new column A
and I put this in A6 (last row that column B uses)
=B6

And then I put this in A5 (up one cell)
=IF(ISNUMBER(B5),A6,B5)
And then I filled that formula up to A1--just by grabbing by the fill handle and
dragging upwards.

I ended up with something that looked like:

MURJ-001 01-Apr-07 amount
MURJ-001 29-Apr-07 amount
MURJ-001 MURJ-001
MURC-001 15-Apr-07 amount
MURC-001 22-Apr-07 amount
MURC-001 MURC-001

Then I selected column A and
edit|copy
Edit|Paste special|values

I wouldn't leave that empty row. I'd delete it.

Select column C
Data|filter|autofilter
filter to show blanks
select those visible rows and
edit|delete row

If you really want those empty rows, use the autofilter the same way, but
instead of deleting those rows, just use Edit|clear|contents

Then turn off the filter.
Data|Filter|autofilter.

I don't like empty rows in my data--they mess up charts, filters, pivottables,
....

 

[Guest]

Try this:

H1 =IF(COUNTIF(F1:F8,0),INDEX(A1:A8,MATCH(0,F1:F8,0)),0)

 

[Guest]

Thanks

Try this:

H1 =IF(COUNTIF(F1:F8,0),INDEX(A1:A8,MATCH(0,F1:F8,0)),0)

 

[Guest]

Thanks

I did this and it seemed to work ok.

First, I copied your sample data into A16--you'll be adjust row 6 to match
your last row of data.

Then I deleted columns B and D

Then I inserted a new column A
and I put this in A6 (last row that column B uses)
=B6

And then I put this in A5 (up one cell)
=IF(ISNUMBER(B5),A6,B5)
And then I filled that formula up to A1--just by grabbing by the fill handle and
dragging upwards.

I ended up with something that looked like:

MURJ-001 01-Apr-07 amount
MURJ-001 29-Apr-07 amount
MURJ-001 MURJ-001
MURC-001 15-Apr-07 amount
MURC-001 22-Apr-07 amount
MURC-001 MURC-001

Then I selected column A and
edit|copy
Edit|Paste special|values

I wouldn't leave that empty row. I'd delete it.

Select column C
Data|filter|autofilter
filter to show blanks
select those visible rows and
edit|delete row

If you really want those empty rows, use the autofilter the same way, but
instead of deleting those rows, just use Edit|clear|contents

Then turn off the filter.
Data|Filter|autofilter.

I don't like empty rows in my data--they mess up charts, filters, pivottables,
....