Copy only non-blank cells

[kosie345]

Hi, I've got a sheet with data in varying positions. I want to copy them all to a new sheet and have them all in one column. I've looked at some of the other examples here but they're terribly advanced.

Thank you

[Richard Buttrey]

Hi,

You've not given us much to work with.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.Hi,

Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not psychic.

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

[PaulM100]

The formula here https://www.excelforum.com/excel-gen...le-column.html , thanks to samba_ravi, helped me in the past.

I've modified it to contain your range and put it in A2:

Please Login or Register  to view this content.

[kosie345]

I hope this makes more sense.

[kosie345]

I can't get this to work. I've pasted it into cell A2 but I don't get the numbers from each row.

[JohnTopley]

Row 11 has 29 and 55

What is expected in C11 ?

[kvsrinivasamurthy]

Pl see file. In C5 then copied.

=IFERROR(INDEX(ACW1781CSV!5:5,AGGREGATE(15,6,COLUMN(ACW1781CSV!$C5:$BL5)/(ACW1781CSV!$C5:$BL5>0),COLUMNS($C5:C5))),"")

[Richard Buttrey]

Are these two names truly representative? Why do they repeat?

What do you mean by "These blank cells I need to remove for all the rows below but they're all in varying locations."
It's not at all clear, at least to me. Your results sheet should accurately reflect what you want to end up with.

[kosie345]

This works perfectly! Thank you so much.