Returning Numeric Results across a Single Row in Consecutive Cells

[Sam via OfficeKB.com]

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

 

[Bob Phillips]

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)

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

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

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

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for assistance. The Formula works Great!

Cheers,
Sam

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

 

[Bob Phillips]

Just adjust the range being returned, after the INDEX

--
HTH

Bob Phillips

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

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

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

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

 

[Sam via OfficeKB.com]

Hi Bob,

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

 

[Bob Phillips]

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]

Hi Bob,

No problem.

Cheers,
Sam

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

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