Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9

This assumes the blanks in A are empty and not blanks from formulas, select
A, press F5
select special, select constants and click OK, press Ctrl + C and paste
wherever you want,
if the values are formulas select formulas instead of constants


--
Regards,

Peo Sjoblom



"Genesis" wrote in message
oups.com...
Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9

On Aug 16, 10:32 am, "Peo Sjoblom" wrote:
This assumes the blanks in A are empty and not blanks from formulas, select
A, press F5
select special, select constants and click OK, press Ctrl + C and paste
wherever you want,
if the values are formulas select formulas instead of constants

--
Regards,

Peo Sjoblom

"Genesis" wrote in message

oups.com...



Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9- Hide quoted text -

- Show quoted text -

Peo,

Thanks. If I understand you correctly, your procedures still requires
manual clicking, copying, pasting. This would still require a macro to
perform these functions. I'm looking for a formula to automatically
remove the blank cells and show only cells with values without PIVOT
and/or MACROS.

Thanks.

Put this in B1, enter it with ctrl + shift & enter and copy down as long as
you want


=IF(ROWS(B$1:B1)<=SUM(N($A$1:$A$100<"")),INDEX($A $1:$A$100,MATCH(SMALL(COUNTIF($A$1:$A$100,"<="&$A$ 1:$A$100),SUM(N($A$1:$A$100=""))+ROWS($A$1:A1)),CO UNTIF($A$1:$A$100,"<="&$A$1:$A$100),0)),"")

Note that if you have a really large list this will be very uneconomic when
it comes to computer resources. It will slows down any large workbook, it's
much better to record a macro and run that macro

--
Regards,

Peo Sjoblom



"Genesis" wrote in message
oups.com...
On Aug 16, 10:32 am, "Peo Sjoblom" wrote:
This assumes the blanks in A are empty and not blanks from formulas,
select
A, press F5
select special, select constants and click OK, press Ctrl + C and paste
wherever you want,
if the values are formulas select formulas instead of constants

--
Regards,

Peo Sjoblom

"Genesis" wrote in message

oups.com...



Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9- Hide quoted text -

- Show quoted text -

Peo,

Thanks. If I understand you correctly, your procedures still requires
manual clicking, copying, pasting. This would still require a macro to
perform these functions. I'm looking for a formula to automatically
remove the blank cells and show only cells with values without PIVOT
and/or MACROS.

Thanks.

On Aug 16, 11:33 am, "Peo Sjoblom" wrote:
Put this in B1, enter it with ctrl + shift & enter and copy down as long as
you want

=IF(ROWS(B$1:B1)<=SUM(N($A$1:$A$100<"")),INDEX($A $1:$A$100,MATCH(SMALL(COU*NTIF($A$1:$A$100,"<="&$A $1:$A$100),SUM(N($A$1:$A$100=""))+ROWS($A$1:A1)),C O*UNTIF($A$1:$A$100,"<="&$A$1:$A$100),0)),"")

Note that if you have a really large list this will be very uneconomic when
it comes to computer resources. It will slows down any large workbook, it's
much better to record a macro and run that macro

--
Regards,

Peo Sjoblom

"Genesis" wrote in message

oups.com...



On Aug 16, 10:32 am, "Peo Sjoblom" wrote:
This assumes the blanks in A are empty and not blanks from formulas,
select
A, press F5
select special, select constants and click OK, press Ctrl + C and paste
wherever you want,
if the values are formulas select formulas instead of constants

--
Regards,

Peo Sjoblom

"Genesis" wrote in message

groups.com...

Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9- Hide quoted text -

- Show quoted text -

Peo,

Thanks. If I understand you correctly, your procedures still requires
manual clicking, copying, pasting. This would still require a macro to
perform these functions. I'm looking for a formula to automatically
remove the blank cells and show only cells with values without PIVOT
and/or MACROS.

Thanks.- Hide quoted text -

- Show quoted text -

Peo,

YOU ARE THE MAN !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks a million!!

Lee

Select the range
Press F5, click on the Special button, then check Constants & click on OK
Copy
Select the cell where you want the list to reside
Edit-Paste special-skip blanks-OK

"Genesis" wrote:

Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9

That was my first answer which he rejected <bg


--
Regards,

Peo Sjoblom



"Duke Carey" wrote in message
...
Select the range
Press F5, click on the Special button, then check Constants & click on OK
Copy
Select the cell where you want the list to reside
Edit-Paste special-skip blanks-OK

"Genesis" wrote:

Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros. The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special -- paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks. I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A B
1 Forks 1 Forks
2 Knives 2 Knives
3 Spoons 3 Spoons
4 4 Chopsticks
5 5 Napkins
6 Chopsticks 6 Cups
7 Napkins 7
8 8
9 Cups 9