|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
|
|
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)
"Mhz" <Mhz.2axcbo_1152861906.1968[ at ]excelforum-nospam.com> wrote in message
news:Mhz.2axcbo_1152861906.1968[ at ]excelforum-nospam.com...
[Quoted Text] >
> 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?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=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:
[Quoted Text] > 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?action=getinfo&userid=35980> View this thread: http://www.excelforum.com/showthread.php?threadid=561376> > |
|
|
|
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
[Quoted Text] > I AM AMAZED !! :eek: 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!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
--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
View this thread: http://www.excelforum.com/showthread.php?threadid=561376
|
|
|
[Quoted Text] > 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 ]
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!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
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=561376
>
>
|
|
|
|
=IF(OR(DAY1!B21<1,DAY1!B21>10),DAY1!C21,"")
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Mhz" <Mhz.2azrd3_1152974708.4579[ at ]excelforum-nospam.com> wrote in message
news:Mhz.2azrd3_1152974708.4579[ at ]excelforum-nospam.com...
[Quoted Text] >
> 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!B21>0,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?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=561376
>
|
|
|
|
|