Returning Numeric Results across a Single Row in Consecutive Cells

Hi All,

How can I Return Numeric Results across a single Row in consecutive cells -
no blank / empty cells?

I am using the Formula below to Return the Results of Numeric Labels that DO
NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:

=IF(AND(D3<>$D$6:$BZ$6,D3<>$D$16:$BZ$16),D3,"") - Array entered.

The Numeric Labels in the Row arrays are in sequential ascending order.

When the Results are Returned I get blank cells in between the Results (for
the Numeric Labels that EXist in the above two Row arrays - $D$6:$BZ$6, $D$16:
$BZ$16):

1 2 blank blank 5 blank blank blank 9 blank 11 etc.

How can I Return the Results without blank cells in between? Filling each
cell consecutively - like this:
1 2 5 9 11

Thanks
Sam

--
Message posted via http://www.officekb.com

Use this array formula

=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D3
:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS
E(ROW($A1:$A20)))),"",
INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(
D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP
OSE(ROW($A1:$A20)))))

--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:63490f00ad995@uwe...
> Hi All,
>
> How can I Return Numeric Results across a single Row in consecutive
cells -
> no blank / empty cells?
>
> I am using the Formula below to Return the Results of Numeric Labels that
DO
> NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:
>
> =IF(AND(D3<>$D$6:$BZ$6,D3<>$D$16:$BZ$16),D3,"") - Array entered.
>
> The Numeric Labels in the Row arrays are in sequential ascending order.
>
> When the Results are Returned I get blank cells in between the Results
(for
> the Numeric Labels that EXist in the above two Row arrays - $D$6:$BZ$6,
$D$16:
> $BZ$16):
>
> 1 2 blank blank 5 blank blank blank 9 blank 11 etc.
>
> How can I Return the Results without blank cells in between? Filling each
> cell consecutively - like this:
> 1 2 5 9 11
>
> Thanks
> Sam
>
> --
> Message posted via http://www.officekb.com

Here's another way...

Assuming that the results are to be returned in Row 25, starting at D25,
leave C25 empty, then try the following formula which needs to be
confirmed with CONTROL+SHIFT+ENTER...

=INDEX($D3:$BZ3,MATCH(0,IF(COUNTIF($D6:$BZ6,$D3:$BZ3)=0,IF(COUNTIF($D16:$
BZ16,$D3:$BZ3)=0,COUNTIF($C25:C25,$D3:$BZ3))),0))

Note that Conditional Formatting can be used to hide #N/A error values.

Hope this helps!

In article <63490f00ad995@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> How can I Return Numeric Results across a single Row in consecutive cells -
> no blank / empty cells?
>
> I am using the Formula below to Return the Results of Numeric Labels that DO
> NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:
>
> =IF(AND(D3<>$D$6:$BZ$6,D3<>$D$16:$BZ$16),D3,"") - Array entered.
>
> The Numeric Labels in the Row arrays are in sequential ascending order.
>
> When the Results are Returned I get blank cells in between the Results (for
> the Numeric Labels that EXist in the above two Row arrays - $D$6:$BZ$6, $D$16:
> $BZ$16):
>
> 1 2 blank blank 5 blank blank blank 9 blank 11 etc.
>
> How can I Return the Results without blank cells in between? Filling each
> cell consecutively - like this:
> 1 2 5 9 11
>
> Thanks
> Sam

Hi Bob,

Thank you very much for your time and assistance. The Formula looks awesome!

I've array entered the Formula but it actually returns the opposite to what I
need. It returns the Numeric Labels that are in Row array $D$6:$BZ$6, $D$16:
$BZ$16.

>I am using the Formula below to Return the Results of Numeric Labels that DO
>NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:

>=IF(AND(D3<>$D$6:$BZ$6,D3<>$D$16:$BZ$16),D3,"") - Array entered.

If time permits, would appreciate a version of your Formula that provides the
Numeric Labels NOT in Row array $D$6:$BZ$6, $D$16:$BZ$16.

Cheers,
Sam

Bob Phillips wrote:
>Use this array formula
>
>=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D3
>:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS
>E(ROW($A1:$A20)))),"",
>INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(
>D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP
>OSE(ROW($A1:$A20)))))
>
>> Hi All,
>>
>[quoted text clipped - 20 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

Hi Domenic,

Thank you for assistance. The Formula works Great!

Cheers,
Sam

Domenic wrote:
>Here's another way...
>
>Assuming that the results are to be returned in Row 25, starting at D25,
>leave C25 empty, then try the following formula which needs to be
>confirmed with CONTROL+SHIFT+ENTER...
>
>=INDEX($D3:$BZ3,MATCH(0,IF(COUNTIF($D6:$BZ6,$D3:$BZ3)=0,IF(COUNTIF($D16:$
>BZ16,$D3:$BZ3)=0,COUNTIF($C25:C25,$D3:$BZ3))),0))
>
>Note that Conditional Formatting can be used to hide #N/A error values.
>
>Hope this helps!
>
>> Hi All,
>>
>[quoted text clipped - 20 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Just adjust the range being returned, after the INDEX

--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:634b691170eff@uwe...
> Hi Bob,
>
> Thank you very much for your time and assistance. The Formula looks
awesome!
>
> I've array entered the Formula but it actually returns the opposite to
what I
> need. It returns the Numeric Labels that are in Row array $D$6:$BZ$6,
$D$16:
> $BZ$16.
>
> >I am using the Formula below to Return the Results of Numeric Labels that
DO
> >NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:
>
> >=IF(AND(D3<>$D$6:$BZ$6,D3<>$D$16:$BZ$16),D3,"") - Array entered.
>
> If time permits, would appreciate a version of your Formula that provides
the
> Numeric Labels NOT in Row array $D$6:$BZ$6, $D$16:$BZ$16.
>
> Cheers,
> Sam
>
> Bob Phillips wrote:
> >Use this array formula
> >
>
>=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D
3
>
>:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPO
S
> >E(ROW($A1:$A20)))),"",
>
>INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH
(
>
>D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANS
P
> >OSE(ROW($A1:$A20)))))
> >
> >> Hi All,
> >>
> >[quoted text clipped - 20 lines]
> >> Thanks
> >> Sam
>
> --
> Message posted via http://www.officekb.com

Hi Bob,

Bob Phillips wrote:

>Just adjust the range being returned, after the INDEX

Not sure what to adjust the range to?

Use this array formula
=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D3
:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS
E(ROW($A1:$A20)))),"",
INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(
D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP
OSE(ROW($A1:$A20)))))


Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Sorry Sam,

I completely misundersttod what you wanted.

My formula could be adapted to work, but as you have a good solution from
Domenic, it is hardly worth it.


--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:634bd42b74889@uwe...
> Hi Bob,
>
> Bob Phillips wrote:
>
> >Just adjust the range being returned, after the INDEX
>
> Not sure what to adjust the range to?
>
> Use this array formula
>
=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D3
>
:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS
> E(ROW($A1:$A20)))),"",
>
INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(
>
D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP
> OSE(ROW($A1:$A20)))))
>
>
> Cheers,
> Sam
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Hi Bob,

No problem.

Cheers,
Sam

Bob Phillips wrote:
>Sorry Sam,

>I completely misundersttod what you wanted.

>My formula could be adapted to work, but as you have a good solution from
>Domenic, it is hardly worth it.
>
>> Hi Bob,
>>
>[quoted text clipped - 3 lines]
>>
>> Use this array formula
>
>=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(D3
>
>:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS
>> E(ROW($A1:$A20)))),"",
>
>INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z$6,0)))+(ISNUMBER(MATCH(
>
>D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP
>> OSE(ROW($A1:$A20)))))
>>
>> Cheers,
>> Sam

--
Message posted via http://www.officekb.com