I need to find / count the duplicate values in different lists.

[stefun]

I'm sorry, but I know nothing about Excel. I know very little about anything when it comes to computers, but I'm here to ask for your kind help. Google just told me that Excel can compute info into statistics, and statistics is what I need.

Me and my friends have ran a test on how colors affect the mind of people, their mood, and each day we gathered from our subjects a lot of sets of colors (not only colors, also descriptions of feelings etc., but it's the colors that we need statistics for).

For example:

lime, orange, yellow
black, silver, red
white, green, red
... etc.

That is an example of a list that contains sets of 3 colors. The list may have an unlimited number of color-sets. And we also have many lists, so if I say "an unlimited number of lists" I might as well be right.

We need to find out what color-sets repeat themselves across the lists, and how many times each, then have them displayed from the most repeated color-set to the least repeated one.

Can Excel do that?

Thank you for taking the time to read. I'm looking forward to your suggetions.

 

[mikerickson]

Yes, Excel can do that.

Is the color set "lime, orange, yellow" the same as the color set "orange, yellow, lime"?

 

[stefun]

Yes, Excel can do that.

Is the color set "lime, orange, yellow" the same as the color set "orange, yellow, lime"?

Oh, goody, I already have a reply. Thank you!

You are asking if the order of the colors in each line matters, right?... That's a good question, I forgot to mention if it does. I should've. Well, it would be great if we could run it both ways. One statistic for when "a,b,c" is the same only with "a,b,c", and another for when "a,b,c" is the same with any order of those 3 letters.

Is it possible? If not, then... which of the two can work? I'll take it.

 

[stefun]

Well, we've decided "lime, orange, yellow" is not the same with "orange, yellow, lime".

So... "1,2,3" is the same only with "1,2,3".

But I didn't address the problem too well in the first place. Let's say I have a number of lists that Excel can't open / work with simultaneously. Let's say there are 100 lists. I'd like to open them in pairs of 20, for an instance. I'd like Excel to list me all sets of colors existing in the 20 lists, tell me tell me how many of each set has found, and list them accordingly from most present one to least present one.

I will do the same with the other pairs of 20 lists. At the end I'll be having 5 result-lists resulting from the 5 sets of 20 lists. Can I add the 5 result-lists together? For example:

Result-list no.1:

43 x Yellow-Blue-Black
21 x White-Red-Silver
10 x Green-Gray-White

Result-list no.2:

32 x Orange-Lime-Navy
31 x Red-Yellow-Blue
29 x White-Red-Silver

List 1 + List 2 would equal:

50 x White-Red-Silver (21 of them in list-1, and 29 in list-2)
43 x Yellow-Blue-Black
32 x Orange-Lime-Navy
29 x White-Red-Silver
10 x Green-Gray-White

That way I can compute a larger number of lists than Excel can compute at once. Could it be done that way, or in any other way, Mike?... Anyone?

 

[learnmore]

How about list first the possible list then use COUNTIF function

 

[mikerickson]

Yes, this is all doable in Excel.
Put the lay-out of your data and the location of you desire output are important in how to do it.

I do want to comment about your 100 list scenario.
Excel can work with 100 lists simultaneously, unless they are truly huge (100,000+ rows per list)

Even if it couldn't work with all 100 simultaneously, there would be no need for to group it in 20's.

Given List1, List2, ...., List100

Create (blank) ListA, ListResult
ListA=List1
let i = 1
*
let i = i+1
Calculate ListResult=ListA+List(i)
Let ListA = ListResult
Loop back to *

will get the result you want.

 

[stefun]

Don't know much about a science book
Don't know much about the french I took

But I do know that I need you...

... as I said, it's the first time I'm using Excel. And I'm not too familiar with other programs either. I'm grateful for your replies, but COUNTIF and "let i =1"... let... it be, let it be... It's martian to me. I was hoping for a step-by-step Excel for Dummies: "Plug your computer to a power source" etc.

My lists are a little over 1.000 rows per list, so I take it that it's a piece of cake for Excel.