Omitting blanks in column data

[Kevin]

I want to automate a process that will do the following:
- copy a column of data
- omit the blank cells
- transpose the remaining non-blank cells into a row.

Example:
A:
Bill
Tracy

Jim

Billy

Would equal:
A B C D E
Bill Tracey Jim Billy

Any help would be greatly appreciated!
Kevin

 

[Harlan Grove]

I want to automate a process that will do the following:
- copy a column of data
- omit the blank cells
- transpose the remaining non-blank cells into a row.

Example:
A:
Bill
Tracy

Jim

Billy

Would equal:
A B C D E
Bill Tracey Jim Billy

...

Select the range containing the names (I'll assume it's A1:A6), run Edit > Goto,
click on the Special button, choose constants, click OK. At this point only the
cells containing names should still be selected. Move to some other range. Run
Edit > Paste Special, choose Transpose and click OK. This should give you the
result range you want.

 

[Kevin]

That works for a manual process.
I am curious about automating this process.
I want my file to go to another file and grab this data
and populate it in the row format without blanks like a
transpose(vlookup()) function that omits blanks.

 

[Harlan Grove]

That works for a manual process.
I am curious about automating this process.
I want my file to go to another file and grab this data
and populate it in the row format without blanks like a
transpose(vlookup()) function that omits blanks.

Turn on the macro recorder while you're doing this. This will result in VBA
code. You'll need to replace the hardcoded cell addresses with range variables.
You can post the recorded code in a follow-up along with DETAILS of PRECISELY
how you want the process to work. You haven't given enough information for me to
venture macro code without making me guess too many particulars.

 

[Gord Dibben]

Kevin

Automate it by turning on the Macro Recorder while you do the manual process
"that works".

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Stephen Dunn]

Hi Kevin,

Seems like you would prefer a formula to a macro solution. Replace $C$2
with a reference to the cell that you type this formula into, and $A$1:$A$10
with a reference to the range containing the data.

=IF(COLUMN()-COLUMN($C$2)+1>COUNTA($A$1:$A$10),"",
INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),
COLUMN()-COLUMN($C$2)+1)))

hold Ctrl+Shift when you press Enter to make it an array formula. Then copy
it along as far as required.

Steve D.

 

[Harlan Grove]

...
...

=IF(COLUMN()-COLUMN($C$2)+1>COUNTA($A$1:$A$10),"",
INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),
COLUMN()-COLUMN($C$2)+1)))

...

Quibble:

COLUMN()-COLUMN($C$2)+1>COUNTA($A$1:$A$10)

is logically equivalent to

COLUMN()-COLUMN($C$2)>=COUNTA($A$1:$A$10)

but the latter does less.

 

[Stephen Dunn]

Conceded. I'm just surprised you didn't add TRIM too. <g>

...
..
..

Quibble:

COLUMN()-COLUMN($C$2)+1>COUNTA($A$1:$A$10)

is logically equivalent to

COLUMN()-COLUMN($C$2)>=COUNTA($A$1:$A$10)

but the latter does less.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something

else.