Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Seemingly Complex Counting

    Hi all,

    Any COUNT formula guru's in?

    I have comma delimited data, with each cell containing one of 3 words, or blank. I have over 500 rows, and each row differs in data quantity, so row 1 may have 3 cells complete, row 2 may have 14 cells complete, etc, with the longest being 88 cells. I have them colour co-ordinated with conditional formatting, so visually, I can see patterns.

    I need a summary section per row. For example, if a row goes like this:
    First Second Second Second Second First First Third Second

    I need the summary to show (in cells adjacent to each other) 1 4 2 1 1

    The numbers will be colour co-ordinated, like below
    Untitled.jpg

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    ..here's a start.

    Formats have been applied up to column [CZ]
    ..enter a, b, or c into any cell from column [G] onwards

    zeddy
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    leeroysilk (2015-06-17)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    ..and if you didn't want to see the a, b, or c characters, just amend the conditional format to include the font colour to be the same as the cell colour

    zeddy

  5. #4
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Zeddy - that is great, but I forgot to mention a minor detail.

    The data you included on row 3, where there are 4 a's - I need them split out to show a 'pathway' kind of thing.

    I've mocked a row in your spreadsheet at row 18RZ Amended.xlsx

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    So, let me get this straight, from your example in row 18, are you saying that
    1. you have an existing sequence (e.g. 2 1 1 3 3 2 as in [A18:F18] ), then, leave a gap of one column, create the 'path as specified for this row' (as in [H18:S18] )

    OR,
    2. are you saying, you have this existing path (as in [H18:S18] ), and then you want to create the 'counts' as in [A18:F18].

    Not sure which way round it is.
    My guess is that it is type 1??????

    zeddy

  7. #6
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Unfortunately not my good man, its type 2.

    I have the pathways, but I need to create the Count Summary

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    I knew that.
    Just testing you.
    So you really are after Count summary then.
    So, my next question is, do you start off with just the paths, all beginning in column A, ..and then, you 'shove' the path to the right as you insert each of the counts?

    zeddy

  9. #8
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    I would be quite happy to move all the paths over to row AA or somewhere to ensure plenty of room, I just need the summary.

    I do have in column A the paths separated by a comma, and the paths I'm trying to summarise are split, comma delimited, so either would do, whichever is easiest.

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    No problem.
    But it would be easier if you could attach a small sample file

    zeddy

  11. #10
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Happily

    Sample.xlsx

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    see attached file.
    It was easier to use VBA rather than formulas.

    Enter your data in column [A]
    I have amended your conditional formatting to allow for up to 100 rows (easy to change this if more required).
    Click button to 'lay slabs'

    zeddy
    Attached Files Attached Files

  13. #12
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hmmm......not really what I wanted, however, I am extremely appreciative of your help.

    The 'slabs' are already in my worksheet, they dont need amending. I need to be able to build the summary.

    Example, in row 31 of the spreadsheet you just attached, the summary should read:
    3 1 2 1

    Thats the summary part I'm after

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi leeroy

    ..look - I'm getting there, and I'm having fun doing it.

    ..back with another version shortly

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    leeroysilk (2015-06-17)

  16. #14
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    You're a legend. Aren't you at work or anything? You've spent a lot of time on this.

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi leeroy

    OK, here we are..

    ..in this version, click the button to count the 'slabs'

    For the counting, the text items and colours are taken from cells [D2], [D3], [D4]

    I have not put any checks in for very long alternating sequences that would go beyond column [Y]

    zeddy
    Attached Files Attached Files

  18. The Following User Says Thank You to zeddy For This Useful Post:

    leeroysilk (2015-06-17)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •