Using Formula based Cell Content Return Unique Consecutive Duplicate Values

[Sam via OfficeKB.com]

Hi All,

I have a Named Range called "Data" that spans 8 columns and 7 rows. A Numeric
Value will appear only once in a row. Each row's Numeric Values are in
ascending order. A unique consecutive duplicate MUST be in the SAME column.

I would like to Return Unique Consecutive Duplicates across a Single Row in
ascending order.

I have a similar Data Layout and Scenario to that listed below. The main
difference is the numeric values in the cells are NOT constants - they are
formulas that pull the resultant numeric vales from another worksheet in the
same workbook. For example, in the Sample Data Layout where it displays
numeric values such as:

101 102 107 110 145 370 490 501

they are actually the end result of a Formula in those individual cells.

Should I be able to use Domenic's Formulae below to get the correct results
from my NEW Sample Data cells that contain Formulas rather than numeric
constants?

Please advise.

Thanks
Sam

Domenic provided me with this great solution for my original scenario which
contained only numeric constants:

Assuming that A2:H8 contains the data, try the following...


...confirmed with CONTROL+SHIFT+ENTER

leave empty

L2, copied across:

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

Original Scenario:
I have a Named Range called "Data" that spans 8 columns and 7 rows. A Numeric
Value will appear only once in a row. Each row's Numeric Values are in
ascending order. A unique consecutive duplicate MUST be in the SAME column.

I would like to Return across a single row unique consecutive duplicates
(single instance
of a consecutive duplicate value in the SAME column) in ascending order.

Sample Data Layout:

101 102 107 110 145 370 490 501
104 106 107 144 360 430 470 580
125 129 140 150 350 430 460 590
101 102 129 130 149 330 440 578
101 108 120 129 200 280 430 535
100 111 170 175 176 280 420 520
121 189 170 202 229 230 420 521

Expected Results: Unique Duplicate Returned across Single Row
101 107 170 280 420 430

Column 1 = 101
Column 3 = 107, 170
Column 6 = 280, 430
Column 7 = 420

 

[Domenic]

Can you post the formula used in your data?

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for reply.

Can you post the formula used in your data?

=INDEX(OFFSET(Data_Dec,0,COLUMNS($A:A)-1,,1),MATCH($B2,Ref,0),COLUMNS(A:A))

The above Formula is for the 1st Cell (1st Row and 1st Column) - copied
across and down.

Named Range "Data_Dec" is numeric values - constants.
B2 is a numeric reference.

Cheers
Sam

 

[Domenic]

Maybe the numbers in the named range Data_Dec are being recognized as
text values. What do you get when you try the following...

=ISNUMBER(A2)

....where A2 refers to a cell located within the named range Data_Dec.
If it returns FALSE, you'll need to coerce them into numerical values.
If this is the case, try the following...

1) Select an empty cell

2) Edit > Copy

3) Select the named range Data_Dec

4) Edit > Paste Special > Add > Ok

Does this help?

 

[Sam via OfficeKB.com]

Hi Domenic,

It recognises them as numbers.

When I used the Formulae you created yesterday on Named Range "Data_Dec" it
returned the first value correctly but when I copied the Formula across it
repeated the first value which is incorrect. I edited the second cell to re-
do the CONTROL+SHIFT+ENTER and it changed it to the correct value but as soon
as my worksheet was was re-calculated it reverted to the incorrect value in
the first cell.

Maybe the numbers in the named range Data_Dec are being recognized as
text values. What do you get when you try the following...

=ISNUMBER(A2)

It returns TRUE

...where A2 refers to a cell located within the named range Data_Dec.
If it returns FALSE, you'll need to coerce them into numerical values.
If this is the case, try the following...

1) Select an empty cell

2) Edit > Copy

3) Select the named range Data_Dec

4) Edit > Paste Special > Add > Ok

Does this help?

No

Cheers,
Sam

 

[Domenic]

I'm not sure off hand where the problem lies. Would you like me to take
a look at a sample of your file? If so, you can email me a copy. Also,
please include only relevant columns, and remove any personal
information.

 

[Sam via OfficeKB.com]

Hi Domenic,

Just got back to post. Will email sample.

Cheers
Sam

 

[Domenic]

Okay, it seems that the ISNA(MATCH(.....)) part of the formula doesn't
like values derived from INDEX(OFFSET(.....)). I even tried eliminating
the OFFSET function...

=INDEX(Data!$C$2:$J$8,MATCH(DEC_LOC!$B2,Data!$A$2:$A$8,0),MATCH(DEC_LOC!D
$1,Data!$C$1:$J$1,0))

....but still nothing. If I remember correctly, I believe this is a
known bug. In any case, change my formula to the following...

=IF(COLUMNS($O$2:O2)<=$M$2,MIN(IF(COUNTIF($N$2:N2,$D$2:$K$7)=0,IF($D$2:$K
$7=$D$3:$K$8,$D$2:$K$7))),"")

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for your time and assistance. That's Great!

=IF(COLUMNS($O$2:O2)<=$M$2,MIN(IF(COUNTIF($N$2:N2,$D$2:$K$7)=0,IF($D$2:$K
$7=$D$3:$K$8,$D$2:$K$7))),"")

Works a treat!

Cheers,
Sam