Omitting blanks in column data

  • Thread starter Thread starter Kevin
  • Start date Start date
K

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
 
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
Click to expand...
...

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.
 
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.
 
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.
Click to expand...

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.
 
Kevin

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

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
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.
 
...
...
=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)))
Click to expand...
...

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.
 
Conceded. I'm just surprised you didn't add TRIM too. <g>


Harlan Grove said:
...
..
..

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
Click to expand...
else.
 
Back
Top