• 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.

Formula Challenge 005 - Count Number of times a sublist occurs in a list

Sajan

Sajan

Excel Ninja
Challenge Name

-- Count the number of times a sublist occurs in a list


Challenge Description

-- This is a formula challenge. As such, only formula submissions are accepted.

-- For two lists named "List" and "Sublist", count the number of times "Sublist" appears in "List".

-- "List" and "Sublist" could be of variable length.

-- Goal is to calculate the count without using any helper cells.


Assumptions

-- You may assume a different location for "List" and "Sublist" than cell A1 and B1

-- You may assume that both lists are in rows

-- Items in the "Sublist" may repeat. However, there needs to be an exact match in "List" for it to be counted.


Sample data and output:

List

One

Two

Three

Two

Two

Three

Four

Two

Three

Two

Five

Six


Sublist

Two

Three

Two


In this case, the output expected is 2 since the sublist occurs twice in list.


Enjoy!

-Sajan.
 
Hi, Sajan!

Checked this yet?

http://www.likeoffice.com/28057/excel-formula#6

I didn't, just found it.

Regards!


EDITED


PS: Checked and doesn't work, so challenge may go on normally. It counts individual cells of SubList within List.
 
Game on!

p.s. I'm working on some particularly NASTY challenges. I hypothesize that when I post them, all replies by Ninja's to actual user's questions will come to a screeching halt!
 
Okay, I think this does it:

=SUM(N(FREQUENCY((List=TRANSPOSE(Sublist))*(ROW(List)-TRANSPOSE(ROW(Sublist))),(List=TRANSPOSE(Sublist))*(ROW(List)-TRANSPOSE(ROW(Sublist))))=ROWS(Sublist)))
 
I don't think my approach could be tweaked somehow to account for 2d arrays, as per the question at http://chandoo.org/forums/topic/counting-consecutive-3-digit-records-from-6-digit-array
 
Hi Jeff,

Very nice! It was shorter than my formula by 5 characters too. :) (Instead of using the same list for the bins array for FREQUENCY(), I used a sequential list, which made the formula a little longer.)


As for adapting this technique for 2-d arrays, that is exactly what I did in response to the other post. I just had to convert the 2-d array to a 1-d array first, and then apply the technique. (I was able to identify a way to eliminate the INDIRECT functions, but only after adding a restriction that the list consist of numbers. I am sure there is a better way... so I am continuing my search.)


I am hoping to see some other techniques also for this challenge.


I have some additional ideas for challenges, but I need to solve them first! So feel free to post your challenges anytime! :)


Regards,

Sajan.
 
Sajan: My top secret, patented formula to dynamically turn a 2d array starting in range A1 into a 1d list is this:

=INDIRECT(ADDRESS(MOD(ROW(INDIRECT("A1:A"&ROWS(Array)*COLUMNS(Array))),ROWS(Array))+1,INT((ROW(INDIRECT("A1:A"&ROWS(Array)*COLUMNS(Array)))-1)/ROWS(Array))+1))


How would you do it?
 
Hi Jeff,

You can also use the following to turn a 2-d array into a 1-d array. However, due to the math used, it only works with numeric arrays.


=ROUND(MOD(SMALL(ROW(Array1)+COLUMN(Array1)% +Array1%%, ROW(OFFSET(A$1,,,ROWS(Array1)*COLUMNS(Array1))))*100, 1)*100,0)


Cheers,

Sajan.
 
Ahh. Whereas my top secret approach handles mixtures. Okay, please burn your monitor screen and swear on the Excel Bible (Walkenbach) not to share my formula.
 
You could always use the approach I used in the other post. That handles mixed content.

http://chandoo.org/forums/topic/counting-consecutive-3-digit-records-from-6-digit-array
 
Ahh.


=INDIRECT("R"&SUBSTITUTE(SMALL(ROW(Array)+COLUMN(Array)%, ROW(OFFSET(A$1,,,ROWS(Array)*COLUMNS(Array)))), ".", "C"), FALSE)


Suddenly I'm not feeling so smug about my top secret, patented formula.


;-)
 
I love that photo. Was from a fun night in the Canary Islands. Maybe I'm singing like a Canary. It's actually on my linked in page too. Most people have a nice, safe photo of them in a suit and tie doing a fake smile. I have a photo that looks like someone stuck a pin in my backside. At least prospective employers know exactly what they are getting ;-)
 
Hi Guys,


Just joined the forum.. Very interesting challenges!!


If I understand this one correctly, you need to find the number of SubList items that occur in List, i.e. in the sample, both Two and Three appear in List and hence the answer is 2.


Jeff, if you add another value to both List and SubList, e.g. Seven, your solution returns 0.


My try (shamelessly stole part of this from another challenge post!)


=SUMPRODUCT(--(COUNTIF(List,T(OFFSET(A$1,SMALL(IF(FREQUENCY(MATCH(SubList,SubList,0),MATCH(SubList,SubList,0))>=1,ROW(SubList)), ROW(OFFSET(A$1,,,SUM(1/COUNTIF(SubList,SubList)))))-1, 0)))>0))
 
Hi Sandeep ,


The items in the Sublist should appear in the List , together , in the same order.


In the example given by Jeff :


One



Two

Three

Two



Two

Three

Four



Two

Three

Two



Five

Six


the highlighted portions are the appearances of Sublist in List.


Narayan
 
You must log in or register to reply here.
Back
Top