Locating and using twin pairs of numbers in a range

[Carl]

I have an application that has a series of ranges based on nine rows
and nine columns each (81 cells), and the cells are either blank or
contain integers. I need to do two things with these ranges. I am
moderately experienced with Excel and have a beginners knowledge of
Visual Basic.

1. Identify which if any of the nine rows have two and only two numbers
(not the same values), and that the two number pairs are identical. It
does not matter which cells in the rows contain the identical number
pairs, only that there are two rows with the same two numbers, and that
the value of these two numbers are known. Call them Pairs.

For example:

A B C D E F G H I

Row1 2 3 6 9
Row2 1 2 3 6 9
Row3
Row4 2 4
Row5 2 4
Row6 1 5
Row7 2 5 9
Row8
Row9 1 4 3 5 6 9

Row 4 and 5 have two numbers and the pairs are identical (2 and 4)


2. Identify which rows have more than two numbers and contain the
either of the pair values (2 and/or 4), and then delete the duplicate
numbers from the rows.

For example, delete the number 2 from Row2 ColC; delete the number 2
from Row7 ColA; delete the number 4 from Row9 ColC.

If anyone knows how to do this, I would be very grateful.

Carl

 

[Biff]

Hi!

Don't have a solution but a couple of questions that might help anyone
working on this:

Row4 2 4
Row5 2 4
Row 4 and 5 have two numbers and the pairs are identical (2 and 4)

Do the "pairs" have to be in that order, 2 and 4. What if one of the rows is
4 and 2?

2. Identify which rows have more than two numbers and contain the
either of the pair values (2 and/or 4), and then delete the duplicate
numbers from the rows.

For example, delete the number 2 from Row2 ColC; delete the number 2
from Row7 ColA; delete the number 4 from Row9 ColC.
Row1 2 3 6 9
Row2 1 2 3 6 9

What about the 2 in Row1?

Biff

 

[Carl]

Thanks, Biff, for the two clarifying questions. First, the "pairs" do
not have to be in any order. Second, you are correct and I missed the 2
in Row1; that would need to be deleted as well.
Carl

 

[Max]

Here's one crack at it using non-array formulas ..

Sample construct available at:
http://cjoint.com/?behQIIGJEa
Locating and using twin pairs of numbers in a range_Carl_gen.xls

Assumptions made:
Source data in A1:I9
Only integers 0 - 9 (single digit)
Only 1 occurence of the "Pairs"
Pairs are identical in order, e.g: 2,4 and 2,4 (not for eg.: 2,4 and 4,2)

Placed in:
J1: =IF(COUNT(A1:I1)=2,"x","")
K1: =IF(J1="x",A1&B1&C1&D1&E1&F1&G1&H1&I1,"")
L1: =IF(K1="","",
IF(AND(COUNTIF($K$1:K1,K1)=2,LEFT(K1,1)<>RIGHT(K1,1)),"x",""))
M1: =IF(OR(COUNT(A1:I1)={0,2}),"",IF(COUNT(A1:I1)>2,"x",""))

Placed in N1, N1 copied to V1:
=IF(A1="","",IF(ISNA(MATCH("x",$L:$L,0)),A1,
IF(AND($M1="x",OR(A1=LEFT(INDEX($K:$K,MATCH("x",$L:$L,0)),1)+0,A1=RIGHT(INDE
X($K:$K,MATCH("x",$L:$L,0)),1)+0)),"",A1)))

J1:V1 selected and copied down to V9

N1:V9 returns the desired results, viz.:

Row1 3 6 9
Row2 1 3 6 9
Row3
Row4 2 4
Row5 2 4
Row6 1 5
Row7 5 9
Row8
Row9 1 3 5 6 9

--

 

[Max]

.. First, the "pairs" do not have to be in any order.

Here's a revised crack at it using non-array formulas
which now covers the above ..

Revised sample at:
http://cjoint.com/?bei1Z6MgzL
Locate and use twin pairs of numbers in a range_Carl_v2.xls

Assumptions made:
Source data in A1:I9
Only integers 0 - 9 (single digit)
Only 1 occurence of the "Pairs"
Pairs can be in any order, e.g: 2,4 and 2,4 or 2,4 and 4,2

Placed in:
J1: =IF(COUNT(A1:I1)=2,"x","")
K1: =IF(J1="x",A1&B1&C1&D1&E1&F1&G1&H1&I1,"")
L1:
=IF(SUMPRODUCT((K1<>"")*(ISNUMBER(SEARCH(LEFT(K1,1),$K$1:$K$9))*(ISNUMBER(SE
ARCH(RIGHT(K1,1),$K$1:$K$9)))))=2,"x","")
M1:
=IF(K1="","",IF(AND(COUNTIF($L$1:L1,L1)=2,LEFT(K1,1)<>RIGHT(K1,1)),"x",""))
N1: =IF(OR(COUNT(A1:I1)={0,2}),"",IF(COUNT(A1:I1)>2,"x",""))

Placed in O1, O1 copied to W1:
=IF(A1="","",IF(ISNA(MATCH("x",$M:$M,0)),A1,IF(AND($N1="x",OR(A1=LEFT(INDEX(
$K:$K,MATCH("x",$M:$M,0)),1)+0,A1=RIGHT(INDEX($K:$K,MATCH("x",$M:$M,0)),1)+0
)),"",A1)))

J1:W1 selected and copied down to W9
O1:W9 returns the desired results

 

[Carl]

Max,

This worked perfectly! I am impressed and grateful for your help.

I would like to become more expert in the applications of advanced
excel formulas like you used. Any advice on how to increase my
knowledge would be appreciated. Are there any decent books or
publications or is it a matter of learning by observing experts and
asking questions?

Thanks again.

Carl

 

[Max]

You're welcome, Carl !
Glad it worked for you ..

.. Are there any decent books or publications or
is it a matter of learning by observing experts and asking questions?

One good way is to hang around these excel newgroups (they're open 24 x 7,
btw <g>). Read and try out the posts / responses given. You're bound to
pick up a lot of useful stuff from the responses provided by the many
experienced and extremely helpful folks around here. Also, try answering
some posts as well, don't be shy ! (Practise never hurts)

If you're interested in books, Debra Dalgleish
maintains a comprehensive listing of Excel books at her:
http://www.contextures.com/xlbooks.html

And you might want to visit some of these Excel sites as well
(taken from a past post):
http://tinyurl.com/8ho6c


--