Generating number sequences of variable lenght in Excel

March 22, 2012 at 20:25:03
Specs: Windows 7
I'm honestly not sure if this can even be done in conventional Excel or not, but it's worth a try;

I would like Excel to generate a count-by-subcount line of the "7 of 12" variety, but i want it to do so by referring to a COUNTIF function for the latter number, and then selecting an appropriate range of cells to refer to for the first number.

So, for example; if COUNTIF returns 8, i want it to generate a set of cells containing 1 of 8, 2 of 8, 3 of 8, etc etc.

I'm assuming here i will want to compose the final function in the fashion of =Y1+X2+Z1, where X2 is simply a text cel with the word 'of', and Z1 is the COUNTIF result.

So, my question is actually twofold; how can i generate the Y1 cell with the count, and how do i get excel to select a target range of cells to put the YXZ1 complex based on a COUNTIF result.
(And ideally, to update if the COUNTIF changes!)

So, is there anybody out there who can help me find a way to do this?


See More: Generating number sequences of variable lenght in Excel

Report •

March 23, 2012 at 17:58:27
Try something like this:

         A             B           C           
1      Name         Criteria     Count
2      Sue            Sue        1 of 4
3      Bill                      2 of 4
4      Sue                       3 of 4
5      Tom                       4 of 4
6      Bill
7      Sue
8      Sue

Enter this in C2 and drag it down to the bottom of your list, in this case, C8:

=IF(ROW()-1 > COUNTIF($A$2:$A$8,$B$2), "" ,ROW()-1 & " of " & COUNTIF($A$2:$A$8,$B$2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 23, 2012 at 21:34:11
Okay, that gives me the sort of count i'd like to see, but now comes the tricky bit. Like your list of names, i've got about 20 different things to count.. so my intended list looks like this;
(Which is an incredible load of redundant typing work if you have to make a few simple changes)

Heerenveen 1 van 1
Waddinxveen 3 van 4
Amsterdam 10 van 10
Dordrecht 10 van 10
Zwolle 10 van 10
Elst 2 van 4
Waddinxveen 4 van 4
Heerlen 1 van 5
Heerlen 2 van 5
DCP Amersfoort 1 van 1

Some of the counts here look a bit odd because i picked a random set somewhere in the middle, but you'll get the idea; Every value in column A gets a count, which then counts -only- on the lines of that value.
Simply setting the criterium to the A cel next to it doesn't seem to want to give more than one countdown. Anyone with an idea here?

Report •

March 24, 2012 at 07:55:56
First, a couple of posting tips:

1 - It helps us help you if you include as much detail in your original question as possible. If you had included your example data in your first post, I would have been able to supply a solution that fit the actual situation instead of spending time on the wrong problem.

2 - Before posting example data in this forum, please click on the blue line at the bottom of this post and read the instructions given there. Those instructions will show you how to post data so that the columns line up, making it easier for us to read.

OK, assuming columns headings in Row 1 as shown in my example, try this in B2 and drag it down:

To start, if the number of items in your list will change, you should use a Named Range. For example, select Column A and name it myList. That way you won't have to change the formula if you add more names to the bottom.

That's what I did for the formula that follows.

FIND("%",A2&"%"&COUNTIF($A$2:A2,A2)))&" of "&COUNTIF(myList,A2)

How it works:


will append a % and count of each occurrence of a name to the end of each name. e.g. if you used it in a column by itself you'd get this:

DCP Amersfoort%1


will strip off the number from the end of that string.

The % (or some other character) is needed to give Excel something to find in order to determine where your text ends and the number begin. You can use any character you want as long as it won't appear in your list of names.

Finally, that stripped off number is concatenated with "of" and the total count of each item myList.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question