How to write out common pairs from a list

[dankos233]

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

 

[SirJB7]

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!

 

[dankos233]

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!

Yes i know, but i want an excel formula to use to do that pair for me not me listing them out myself.

 

[dankos233]

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!

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

thanks.

 

[shrivallabha]

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

thanks.

Could you explain your logic for pairs for me? I did not understand it. Are the numbers any way associated with alphabets or it is a separate query?

 

[dankos233]

Could you explain your logic for pairs for me? I did not understand it. Are the numbers any way associated with alphabets or it is a separate query?

Thanks.

Here is an example of how is looks like:

 

[dankos233]

Thanks.

Here is an example of how is looks like:

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.

 

[shrivallabha]

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.

 

[dankos233]

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.

 

[dankos233]

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.

 

[dankos233]

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!

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

 

[shrivallabha]

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.

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,","))))

 

[NARAYANK991]

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

 

[dankos233]

Thanks but still not working the way i expected it to work. see the attach and how i want it to be.

Thank You. i must admit you are very good.

 

[dankos233]

T

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

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]

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?

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.

 

[NARAYANK991]

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

 

[dankos233]

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

Thanks a lot i now get the tricks

You are the best.