Another Formula Question!

[mlv]

I have a column of eleven non-sequent cells that may or may not contain a
currency value.

I need to copy this information across to another column of sequent cells,
but I only wish to copy the cells that contain a value. Cells that are blank
(empty) or showing £zero should be ignored.

For example:

non-sequent data source cells:

C1 £50.00
C3 £100.00
C5 £0.00
C7 £10.00
C9 'Blank'
C11 'Blank'
C14 £60.00
C16 £30.00
C18 'Blank'
C20 'Blank'
C22 'Blank'


Result:

A1 £50.00 (from C1)
A2 £100.00 (from C3)
A3 £10.00 (from C7; C5 ignored)
A4 £60.00 (from C14: C9 & C11 ignored)
A5 £30.00 (from C16)
A6 'Blank'
A7 'Blank'
A8 'Blank'
A9 'Blank'
A10 'Blank'
A11 'Blank'

Can this be achieved without resorting to a macro?

Does anyone have the time to point me in the right direction?

Thanks

 

[MartinW]

Hi mlv,

Select all of your data in column C
Tap F5
Click Special
Check Constants and OK
Copy and paste the selected data to A1

(Depending on what is in the blank cells you may
need to uncheck Text, Logicals and Errors
after checking Constants)

HTH
Martin

 

[mlv]

Select all of your data in column C
Tap F5
Click Special
Check Constants and OK
Copy and paste the selected data to A1

(Depending on what is in the blank cells you may
need to uncheck Text, Logicals and Errors
after checking Constants)

Hi Martin, thanks for the reply.

On re-reading my original post, I realise I didn't make my requirements very
clear.

I would like a formula that automatically copies all of the valid data from
the eleven designated C cells across into the A cell column and that will
auto-update the A cell column entries if any C cell data subsequently
changes.

Basically, all the valid data from the eleven designated C cells (which
could be anything from no valid entries to eleven valid entries) should be
copied across into the A cell column range, starting at cell A1 and using as
many A cells as necessary, e.g. If cells C1, C3 and C14 contain valid
(non-zero, non-blank) entries, then these three entries should be
automatically copied in the same order into cells A1, A2 and A3. If a valid
entry is subsequently put into cell C9, then the new entry in cell C9 should
be copied to cell A3 and the existing entry in cell A3 (copy of cell C14
data) should now appear in cell A4. If all eleven designated C cells have
valid entries, then the data should be copied in the same order into cells
A1 to A11.

Hmm, it's starting to sound a bit complicated. Maybe I should continue with
manually copying the valid data across?

 

[MartinW]

Hi mlv,

Try this,
Insert a helper column C (so your values are now in D)

Put this in C1 and drag down to C22
=IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))

Put this in A1 and drag down to A11
=IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))

If needed hide column C.

HTH
Martin

 

[MartinW]

Just had another thought after posting,
If there are text values inbetween your data
then you may need to add ISTEXT to the OR function like this
=IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))

HTH
Martin

 

[MartinW]

Must be time for bed, change that last correction to

=IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")

HTH
Martin

 

[mlv]

Must be time for bed, change that last correction to

=IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")

HTH
Martin

Thanks for all your help Martin, I'll try the formula out tonight.

(BTW, I can only access this newsgroup at work - I can't find it on the
Tiscali news server that I use at home).

 

[Sandy Mann]

Hi Mike,

As you appear to be using Outlook Express at work I am puzzled why you are
not using it at home, always assuming that you are using Windows.

Dave MdRitchie has some instructions here:

http://www.mvps.org/dmcritchie/ie/oe6nws01.htm


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MartinW]

Hi mlv,

Best to ignore that last formula I posted. ISNUMBER doesn't ignore
the zero values. I think the first correction with ISTEXT should be
the way to go.

HTH
Martin

 

[mlv]

Hi Mike,

As you appear to be using Outlook Express at work I am puzzled
why you are not using it at home, always assuming that you are
using Windows.

Dave MdRitchie has some instructions here:

http://www.mvps.org/dmcritchie/ie/oe6nws01.htm

Hi Sandy

I am using Windows XP SP2 and Outlook Express at home.

I have recently subscribed to Tiscali Max Broadband and I connect to the
Tiscali news server. There are quite a few Microsoft Excel ngs on the
Tiscali server, but not <microsoft.public.excel>. The nearest is
<microsoft.public.excel.misc>.

I emailed Tiscali Technical Help somewhere on a distant planet to ask about
the missing ng, but quite honestly I would have got a more comprehensible
response if I had emailed my neighbour's goldfish.

I guess I need to set up another connection directly to the Microsoft
newsservers.

Thanks for the link.

Regards

 

[MartinW]

Hi mlv,

I just re-read all the posts and realised that I had completely
confused everything with corrections and typos in the corrections
and....?.......anyway here is what should work with the helper
column C in place.

Place this in A1 and drag down to A11.
=IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))

Place this in C1 and drag down to C22.
=IF(OR(D1="",D1=0,ISTEXT(D1)),"",COUNT(INDIRECT("D1"&ROW())))

Ensure that columns A and D have the same currency format.

Put your example C1, C3 etc. values into their corresponding D cells.

Does that do what you need?
Martin

 

[mlv]

Hi mlv,

I just re-read all the posts and realised that I had completely
confused everything with corrections and typos in the corrections
and....?.......anyway here is what should work with the helper
column C in place.

Place this in A1 and drag down to A11.
=IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))

Place this in C1 and drag down to C22.
=IF(OR(D1="",D1=0,ISTEXT(D1)),"",COUNT(INDIRECT("D1"&ROW())))

Ensure that columns A and D have the same currency format.

Put your example C1, C3 etc. values into their corresponding D cells.

Does that do what you need?
Martin

Hi Martin

Yes, the routine seems to be spot on. I tried it in a new worksheet and it
did everything I wanted.

I'll now transfer it into my workbook and test it again.

I like the cunning 'helper column' approach.

Thanks for taking the time to help me with this.

Regards

 

[MartinW]

Good to hear that Mike. Thanks for your reply and
sorry about the confusion in the middle.

(Note to self. Stop trying to solve problems when it is late
at night, and too many beers have gone from the fridge)

<g>
Martin