Another Formula Question!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 28 Sep 2007 11:54 GMT

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

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

MartinW - 28 Sep 2007 12:20 GMT

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

>I have a column of eleven non-sequent cells that may or may not contain a
>currency value.
[quoted text clipped - 38 lines]
>
> Thanks

Reply to this Message

mlv - 28 Sep 2007 16:39 GMT

Martin W wrote:

> Select all of your data in column C
> Tap F5
[quoted text clipped - 5 lines]
> 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?

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

MartinW - 28 Sep 2007 18:55 GMT

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:D"&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

> Martin W wrote:
>>
[quoted text clipped - 32 lines]
> Hmm, it's starting to sound a bit complicated. Maybe I should continue
> with manually copying the valid data across?

Reply to this Message

Rate this thread: