Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Alternative to using IF function to extract data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Alternative to using IF function to extract data
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 With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Alternative to using IF function to extract data
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,
....



Rayasiom wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,718
Default Alternative to using IF function to extract data
Try this:

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


"Rayasiom" wrote:

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 With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Alternative to using IF function to extract data
Thanks

"Teethless mama" wrote:

Try this:

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


"Rayasiom" wrote:

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 With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Alternative to using IF function to extract data
Thanks

"Dave Peterson" wrote:

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,
....



Rayasiom wrote:

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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
AGAIN... I need another Lookup Function to extract some data BillReese Excel Worksheet Functions 2 May 9th 06 12:26 AM
How to select data series to format? (alternative needed) [email protected] Charts and Charting in Excel 2 April 20th 06 08:53 PM
alternative function to sumif Rich Excel Discussion (Misc queries) 1 December 16th 05 09:21 AM
Alternative for IF function Dez Excel Discussion (Misc queries) 1 September 21st 05 10:05 AM
Excel If function alternative saborbas Excel Worksheet Functions 6 April 24th 05 12:18 PM


All times are GMT +1. The time now is 05:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement