• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to write out common pairs from a list

D

dankos233

New Member
i have a little problem which i want solution.
i have a table like this:
a,b,c,d,j on separate column
e,a,d,f,m
g,h,c,a,s
b,d,g,e,d

how will i look for the pairs in this table
because the pairs here are
a,d
c,a
d,e

and which formula will i use to find the pairs. please help me excel Gurus.
and another one is
if they are figure like
10,20,21,26,80
10,45,42,44,80
15,21,23,26,32
45,42,74,76,50
26,10,45,88,89
which formula can i use to bring out all the pairs in the list of items
 

Attachments

Hi, dankos233!

For the letters sample, shouldn't these be the pairs?
a,d
a,c
b,d
e,d
And these for the numbers?
10,80
42,45
10,26
10,45

Regards!
 
SirJB7 said:
Hi, dankos233!

For the letters sample, shouldn't these be the pairs?
a,d
a,c
b,d
e,d
And these for the numbers?
10,80
42,45
10,26
10,45

Regards!
Click to expand...
Yes i know, but i want an excel formula to use to do that pair for me not me listing them out myself.
 
SirJB7 said:
Hi, dankos233!

For the letters sample, shouldn't these be the pairs?
a,d
a,c
b,d
e,d
And these for the numbers?
10,80
42,45
10,26
10,45

Regards!
Click to expand...
Hi,SirJB7, how will i use excel formula to derive those pairs from a thousand of rows.

thanks.
 
dankos233 said:
Thanks.

Here is an example of how is looks like:
Click to expand...

i need a working excel formula that can look through those list and identify all the pairs in it and then write them out in where we have common pairs, and also, how many times have those pair occurred in that long list.

I think is now clear.

Thank you.
 

Attachments

Sorry, that is more data but no explanation.

Do you mean the following:
- In row 2 we have 21,77 which is again occurring in row 101. Is it a pair?
- Does the pair need to be in the same column so as to be identified as pair (yes or no)?
- Since we are looking at C(5,2), total combinations are 10 assuming 21,77 and 77,21 as same combination. Is it a case or is it otherwise?
- Is your real setup having 5 columns only or there will be more columns in future?

Please post clear explanation for us so as to suggest conclusive solution.

Edit: I had some time on hand so have worked something out. It tests all 10 combinations downwards. Attached herewith file. Refer column G to P.
 

Attachments

Last edited:
yes is a pair, and is what am looking for but with a filtre to display only appear >=2 times.

thanks you are the best and have solved it for me in a half way. left with the filtre condition.

thank you.
 
4,82 appears 2 times in the list but in your result, it said 0 times. the position of the numbers doesn't matter all we need is to count how many times the 4,82 appeared in the list presented. this goes to all the pairs you have given, not minding the position they are on the column.

thank you.
 
SirJB7 said:
Hi, dankos233!

For the letters sample, shouldn't these be the pairs?
a,d
a,c
b,d
e,d
And these for the numbers?
10,80
42,45
10,26
10,45

Regards!
Click to expand...

Yes they are but how can you use excel formula to locate and write them out for me.
here is example of such table.

see also a friend tried and solve it half way for me but left with the filtres to separate the pairs. cos i need to display only occurenence >=2 times
 

Attachments

dankos233 said:
4,82 appears 2 times in the list but in your result, it said 0 times. the position of the numbers doesn't matter all we need is to count how many times the 4,82 appeared in the list presented. this goes to all the pairs you have given, not minding the position they are on the column.

thank you.
Click to expand...
That is because I misread your requirements. See attached file.

Input cells: G1, H1

Apply filter for "TRUE" in Column F (Formula to be copied down till last row)
=AND(ISNUMBER(SEARCH(","&$G$1&",",CONCATENATE(",",A2,",",B2,",",C2,",",D2,",",E2,","))),ISNUMBER(SEARCH(","&$H$1&",",CONCATENATE(",",A2,",",B2,",",C2,",",D2,",",E2,","))))

Formula in cell J1 calculates results for given pair
=SUMPRODUCT(--ISNUMBER(SEARCH(","&$G$1&",",CONCATENATE(",",A2:A315,",",B2:B315,",",C2:C315,",",D2:D315,",",E2:E315,","))),--ISNUMBER(SEARCH(","&$H$1&",",CONCATENATE(",",A2:A315,",",B2:B315,",",C2:C315,",",D2:D315,",",E2:E315,","))))
 

Attachments

Hi ,

This will be very easy if we use VBA , but since you are not comfortable with it , the solution is quite involved.

I am not so conversant with formulae , and so this solution is a combination (!) of steps.

1. Use the LARGE function to sort each row of 5 numbers.

2. Use formulae to convert this 5 column table into a single-column list.

3. Create a pivot table and use a value filter to hide the combinations with a count of 1.

The result is in the attached file.

Narayan
 

Attachments

T
NARAYANK991 said:
Hi ,

This will be very easy if we use VBA , but since you are not comfortable with it , the solution is quite involved.

I am not so conversant with formulae , and so this solution is a combination (!) of steps.

1. Use the LARGE function to sort each row of 5 numbers.

2. Use formulae to convert this 5 column table into a single-column list.

3. Create a pivot table and use a value filter to hide the combinations with a count of 1.

The result is in the attached file.

Narayan
Click to expand...

Thanks A perfect solution

i will try and study it.

Thanks a lot cos is excellent match of what i want.

can you use VBA to do same and attach to for me to see and study?
 
dankos233 said:
T


Thanks A perfect solution

i will try and study it.

Thanks a lot cos is excellent match of what i want.

can you use VBA to do same and attach to for me to see and study?
Click to expand...


I Altered the numbers to see if it works, but the only problem was in the common pairs and number of occurrence. please more help please. and thanks a lot

here is the alteration i made.
 

Attachments

Hi ,

Since your earlier workbook had entries till row 314 , the formulae had been written till that row.

The file you have uploaded now , has entries till row 335.

When your data increases , you need to do the following :

1. Copy the formulae in the range K314:O314 downwards , as far as your data extends. I have now copied it till row 335.

2. Refresh the pivot table by clicking on the Refresh All button in the Ribbon.

Narayan
 

Attachments

NARAYANK991 said:
Hi ,

Since your earlier workbook had entries till row 314 , the formulae had been written till that row.

The file you have uploaded now , has entries till row 335.

When your data increases , you need to do the following :

1. Copy the formulae in the range K314:O314 downwards , as far as your data extends. I have now copied it till row 335.

2. Refresh the pivot table by clicking on the Refresh All button in the Ribbon.

Narayan
Click to expand...

Thanks a lot i now get the tricks

You are the best.
 
You must log in or register to reply here.
Back
Top