killing empty spaces in unusall fashion ..

  • Thread starter Thread starter Mhz
  • Start date Start date
M

Mhz

I am trying to create a sheet that will read 2 columns of another sheet
that has empty spaces between the data (Rows) and I want to re-create
the data in a new sheet that will re-fill the columns without the
spaces.. Thanks

e.g. :

_COL_A_______COL_B_

apples 50
grapes 70

bannanas 40

peaches 80

TO:

_COL_A_______COL_B_

apples 50
grapes 70
bannanas 40
peaches 80
 
Create a criteria range with row 1 as the data heading for the source data
in row 1 and <> in row 2, and then use Data>Filter>Advanced Filter with
'Copy to another location box checked, enter the criteria range and the
target cell, and OK.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
To do this manually, just create a list of numbers in column C ie 1, 2,
3 down the page. Then sort by one of the first 2 columns. Delete the
rows at the bottom, then sort by the column with the numbers.

Matt
 
If you want the results dynamic in the other sheet,
here's one play using non-array formulas ..

Assume source data in Sheet1, cols A & B,
data from row2 down to a max expected row100 (say)

In Sheet2,

Put in A2
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2:
=IF(Sheet1!A2="","",ROW())
(Leave C1 empty)

Select A2:C2, copy down to C100
(just copy down to cover the max expected data range in Sheet1)

Cols A and B will return the required results, all neatly bunched at the top
(Hide away the criteria col C, if necess.)

Note that the criteria in col C above simply checks/focuses on whether
Sheet1's col A is empty. If Sheet1's col A is empty on that row, it's deemed
the row is empty.
 
I AM AMAZED !! :eek: MAX, I can't Thank You Enough! That is exactl
what I was trying to Accomplish... Between You, Bob and Others, I
Appears Nothing can't be accomplished With this Program... I'm Ver
Delighted... Thanks Much :)


:cool
 
You're welcome! Glad it fit what you wanted here ..

Btw, in your other post (Re: Summary Sheet For Identified Dupes), I've
responded with a sample construct customized to suit your actual layout. Let
me know how it went for you over there.
 
Thanks Very Much Max! I have one more problem that has given me a
thousand grey hairs since you solved my Spacing problem...

=IF(DAY1!B21>0,DAY1!C21,"")

The above formula I am using returns The name of the Caller if the dupe
proves true (B21>0), anything above the 0 represent a duplicate.

The Problem is that B6:B35 (Representing Dupe Counts in the 31 sheets),
creates SUM Values (dupes Counted Minus 930 cells '30 rows X 31 sheets'
being checked) from the dupe formula you presented to me when the cells
are blank.

eg. the 31 sheets X 30 Rows (B6:B35) will show the sum of the blank
cells of (B6:B35 X 31 sheets)... So when I try to use the
=IF(DAY1!B21>0,DAY1!C21,"") Formula, I get false results above zero due
to the blank cells showing the sum values from the Original Dup Formula
that checks each sheet.

For instance if 40 dupes exists, then (930-40 = 890) the 890 appears
in the blank B6:B35 cells. Therefore my formula check for anything
above zero will render those blank cells as dupe counts due to the SUM
values of the dupe formula, "
=SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!E6:E35"),"="&E9))
"... This formula exists in every E6:E35 cell on all 31 sheets.

I don't want to tamper with the Original Dupe finding formula, but I do
need a formula in the "Spacing" Formula you presented
(=IF(ROW(A1)>COUNT($E:$E),"",INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
), that will bypass any Zeros "0" ...

Hope I'm not being confusing :confused: but any help here is much
appreciated... Thanks
 
I don't want to tamper with the Original Dupe finding formula, but I do
need a formula in the "Spacing" Formula you presented
(=IF(ROW(A1)>COUNT($E:$E),"",INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
), that will bypass any Zeros "0" ... [< returned in col B ]
Click to expand...

In Sheet2,

Instead of
Put in C2:
=IF(Sheet1!A2="","",ROW())
Click to expand...

just tweak the criteria to read Sheet1's col B as well
(i.e. to ignore zeros in col B as well)

Put instead in C2, copy down:
=IF(OR(Sheet1!A2="",Sheet1!B2=0),"",ROW())

There's *no change* to the formulas in A2 and B2,
viz. stick with the previous:
Put in A2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2
Click to expand...


---
 
Yes Max, You have made me a true believer in Miracles ;) .. You
solution to the zero problem works beautifully! Thanks Onc
Again...

By the way, Would we have been able to modify the;

=IF(DAY1!B21>0,DAY1!C21,"") Formula to *exclude* anything Under 1 an
Above 10 ...

Just Curious.. If Not, I'm sticking with the recent solution you jus
gave.. Thanks :
 
=IF(OR(DAY1!B21<1,DAY1!B21>10),DAY1!C21,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Another Great Solution For Me! Thanks Very Much bob..:) Tha
Formula will help me in many situations regarding <Greater than Les
than>...

It Works Flawlessly.. Thank
 
Back
Top