Copy cells to adjacent column, removing blanks

**jbitluk** · 12-08-2009, 02:19 PM

Hi Guys,

There's probably a quick solution to this...

In a worksheet, J2:J52 contains a list of unique strings; each cell populated by an embedded formula. Some of the cells contain a valid 'blank' "" given by the formula.

I'd like K2:K52 to contain the values J2:J52, with all the populated cells stacked at the top and the 'blank' cells at the bottom. 'Blank' cells do not have to be in any particular order.

I've been fiddling with the formula kindly provided by WindKnife on the second post in the thread below for the past couple of hours, but to no avail:

http://www.excelforum.com/excel-work...om-a-list.html

Columns A:H in the same worksheet are populated and unavailable for use.

Any help is gratefully appreciated.

Regards,
Julian

**DonkeyOte** · 12-08-2009, 03:24 PM

If as you say the values are names (strings) and unique then one option might be:

Please Login or Register  to view this content.

obviously if you can store the COUNTIF in one cell and refer to it thereafter

**jbitluk** · 12-11-2009, 01:53 PM

Many thanks.

The solution works well with data inputted to the cells directly (static data).......but not with my data. The solution above results in the first non-blank cell found in J2:J52 being repeated down to row X in K2:K52, where X is the number of non-blank cells in J2:J52.

J2:J52 contain the formula:
[J2] =IF(C2=1,A2,"")
[J3] =IF(C3=1,A3,"")
etc.

The data in A2:A52 comes from another sheet in the same workbook.
Do you have any further thoughts on this please?

Cheers
Julian

**DonkeyOte** · 12-11-2009, 02:00 PM

Julian, please post a sample file with the formulae in place which illustrates it not working.

We are concerned only with seeing contents of A2:A52,C2:C52,J2:K52 ... and for A2:A52 simply override existing formulae with just the output - no other data in the file is of interest at that point.
obviously dummy data values in A2:A52 if confidential (but ensure the dummy values are of the same data type as the originals)

**DonkeyOte** · 12-11-2009, 02:18 PM

And just to be clear --- the formula provided is not an Array and should be committed with ENTER as normal.

If you committed the formula as an Array over the range K2:K52 then that would generate the output you mention.

**jbitluk** · 12-11-2009, 02:38 PM

I copied and pasted your formula into cell K2, pressed ENTER, then dragged the cell contents to the rest of the column. Is that correct?

**DonkeyOte** · 12-11-2009, 02:54 PM

See attached proof of concept with formulae in K based off J via A/C.

Note for sake of demo I added a RAND function to C such that the output will vary with each calculation - ie to validate the concept.

If you're unable to get this to work you will need to post a file accordingly.

**jbitluk** · 12-11-2009, 03:03 PM

Appreciated.

The odd thing is when I overwrite A2:A52 with static data, the formula in K2:K52 behaves itself and does what you say.

Therefore, the contents of cells A2:A52 must be the issue.
These are:
=INDIRECT("Sheet2!AC20")
=INDIRECT("Sheet2!AC30")
etc to
=INDIRECT("Sheet2!AC510")

Using INDIRECT was the only way I could get the values to appear in A2:A52 correctly.
Does this help?

**DonkeyOte** · 12-11-2009, 03:14 PM

To be honest without seeing a file that's doing what you say I am, at best, making educated guesses.

What I would say is that INDIRECT is Volatile so every time XL recalculates so those cells (A2:A52) will recalculate which in turn means J & K are susceptible to change should either A/C alter (thereby altering J thereby altering K)

You say you had to use INDIRECT - why is this ?
IMO the only real "justification" for INDIRECT in this instance would be if you were physically deleting and replacing Sheet2, is that the case ?

If not you should really use an INDEX based approach given it's non-volatile

Please Login or Register  to view this content.

Note: you said down to AC510 - shouldn't A52 be referencing AC520 ?
If not shouldn't then A2 be referencing AC10 rather than AC20 - if so change the INDEX formula above from A$1 to A$2