Alternative to using IF function to extract data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rayasiom - 16 May 2007 23:04 GMT

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.

Reply to this Message

Dave Peterson - 17 May 2007 00:26 GMT

I did this and it seemed to work ok.

First, I copied your sample data into A1:D6--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,
...

> Hi All,
> I receive a client data spreadsheet from my supplier that they can't change
[quoted text clipped - 36 lines]
>
> Thanks.

Signature

Dave Peterson

Reply to this Message

Rayasiom - 17 May 2007 10:18 GMT

Thanks

> I did this and it seemed to work ok.
>
[quoted text clipped - 82 lines]
> >
> > Thanks.

Reply to this Message

Teethless mama - 17 May 2007 05:31 GMT

Try this:

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

> Hi All,
> I receive a client data spreadsheet from my supplier that they can't change
[quoted text clipped - 36 lines]
>
> Thanks.

Reply to this Message

Rayasiom - 17 May 2007 10:17 GMT

Thanks

> Try this:
>
[quoted text clipped - 40 lines]
> >
> > Thanks.

Reply to this Message