Condensing tables of data

[TheRetroChief]

Good morning excelmeisters,

Hopefully an easy problem to ease you into the morning...

Let's assume, starting at A1, that i have table (8c x 30r)
and lets assume that in each of the columns i have 5 cells filled in with data at random.
What is the best way for me to produce a list of the data in each of the columns without having to go in and delete out all the blanks myself? I.e. i want to reproduce the table but without any of the cells which do not have any data in.

Much obliged,
Ali Mac

[royUK]

Not clear what you mean. Is the complete row empty?

[sweep]

Hi,

You could...


Select the whole table,

Edit > go to > Special > Blanks

Right click > delete > shift cells up

[TheRetroChief]

I need to be able to look at the data and it spit it out elsewhere. It needs to be preset to do without user intervention. Some kind of lookup up something that ignores blanks...i dunno really.

this is what i need to happen...hope it comes out correctly

[TheRetroChief]

A B C Goes to... A B C
1 ABC ABC PQR BCD
2 DEF STU EFG
3 PQR GHI VWX
4 JKL YZa
5 BCD MNO
6 DEF
7 STU EFG
8
9 GHI VWX
10
11 JKL
12
13 YZA
14
15 MNO

[TheRetroChief]

Nope that didnt work.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[TheRetroChief]

Ok, lets say that in column A i have data in A2, A7, A16 and A20

I need a function in another spreadsheet that looks at that data, ignores the blanks and returns the data into A1-A4 in the spreadsheet the function/macro runs

[TheRetroChief]

okay, example attached. Haven't used actual data and not bothered with any kind of formatting for presentation purposes.

[sweep]

Please see attached. You should be able to modify it to get the results you need.

[royUK]

Why not just sort the column?

[sweep]

OP was looking for an "automated" option.

I need to be able to look at the data and it spit it out elsewhere. It needs to be preset to do without user intervention.

[TheRetroChief]

Sweep, Top work. Works a treat. I should be able to adapt to any future needs.
Just out of interest, can my ranges be undefined i.e. A:A and B:B
And what about this bit? -ROW($A$1)+1),ROWS(B$1:B1))
Does this have to be the same every time?
A translation into english as to what it's doing would be helpful so i can learn from these things, but not desperately required.
Ta muchly.