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


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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 DataFilterAdvanced 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)

"Mhz" wrote in message
...

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


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
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


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

I AM AMAZED !! MAX, I can't Thank You Enough! That is exactly
what I was trying to Accomplish... Between You, Bob and Others, It
Appears Nothing can't be accomplished With this Program... I'm Very
Delighted... Thanks Much :)





--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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

Btw, in your other post ( 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
I AM AMAZED !! MAX, I can't Thank You Enough! That is exactly
what I was trying to Accomplish... Between You, Bob and Others, It
Appears Nothing can't be accomplished With this Program... I'm Very
Delighted... Thanks Much :)

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!B210,DAY1!C21,"")

The above formula I am using returns The name of the Caller if the dupe
proves true (B210), 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!B210,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,MATC H(SMALL($E:$E,ROW(A1)),$E:$E,0)))
), that will bypass any Zeros "0" ...

Hope I'm not being confusing but any help here is much
appreciated... Thanks


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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,MATC H(SMALL($E:$E,ROW(A1)),$E:$E,0)))
), that will bypass any Zeros "0" ... [< returned in col B ]

In Sheet2,

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
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!B210,DAY1!C21,"")

The above formula I am using returns The name of the Caller if the dupe
proves true (B210), 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!B210,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,MATC H(SMALL($E:$E,ROW(A1)),$E:$E,0)))
), that will bypass any Zeros "0" ...

Hope I'm not being confusing but any help here is much
appreciated... Thanks


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

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

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

=IF(DAY1!B210,DAY1!C21,"") Formula to *exclude* anything Under 1 and
Above 10 ...

Just Curious.. If Not, I'm sticking with the recent solution you just
gave.. Thanks :)


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

=IF(OR(DAY1!B21<1,DAY1!B2110),DAY1!C21,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

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

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

=IF(DAY1!B210,DAY1!C21,"") Formula to *exclude* anything Under 1 and
Above 10 ...

Just Curious.. If Not, I'm sticking with the recent solution you just
gave.. Thanks :)


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376

Another Great Solution For Me! Thanks Very Much bob..:) That
Formula will help me in many situations regarding <Greater than Less
than...

It Works Flawlessly.. Thanks


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561376