Counting Multiple fields in 1 column

1. Counting Multiple fields in 1 column

I don't know if this is possible, but you lot are wizards so I'll give it a go

I have some software that outputs multiple fields into one column. These fields are separated by a ** (attached)

Is it possible to dynamically count how many times (in this case how many 1000's) there is a value between fields separated by the **?

The yellow shows the correct numbers but this is done manually

Sean

2. Re: Counting Multiple fields in 1 column

With data starting in A2...

Helper in B2:
=IF(LEFT(A2,2)="**",A2,B1)

Array formula in C5:
=IFERROR(INDEX(A:A,SMALL(IF(LEFT(\$A\$2:\$A\$24,2)="**",ROW(\$A\$2:\$A\$24)),ROWS(\$1:1))),"")

Formula in D5:
=IF(C5="","",COUNTIF(B:B,C5)-3)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

3. Re: Counting Multiple fields in 1 column

Inspired by Glenn Kennedy
C4=IFERROR(INDEX(A:A,SMALL(IF(LEFT(\$A\$1:\$A\$24,2)="**",ROW(\$A\$1:\$A\$24)),ROWS(\$1:1))),"")
(As suggested by Glen Kennedy)
D4=SUM(INDEX((LOOKUP(ROW(\$A\$1:\$A\$23),ROW(\$A\$1:\$A\$23)/(LEFT(\$A\$1:\$A\$23,2)="**"),\$A\$1:\$A\$23)=\$C4)*(\$A\$1:\$A\$23=1000),0))
Try the above formulas and copy towards down

4. Re: Counting Multiple fields in 1 column

Absolutely genius!

I'd gone down the path of making a count of how many "**" there are then assigning each category I wanted counting an ID number. And then counting how many times that ID happened

Your method is far superior and cleaner, thank you!

5. Re: Counting Multiple fields in 1 column

A non-array alternative for C5:
=IFERROR(INDEX(A:A,SMALL(INDEX((LEFT(\$A\$2:\$A\$24,2)<>"**")*10^10+ROW(\$A\$2:\$A\$24),0),ROWS(\$1:1))),"")

and an alternative for D5:
=IFERROR(1/(1/SUMPRODUCT(--(\$B\$2:\$B\$24=C5)*(ISNUMBER(\$A\$2:\$A\$24)))),"")

6. Re: Counting Multiple fields in 1 column

Glenn Kennedy

Thanks again, I ended up going for your non-array version to keep things moving fast. Works perfectly!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

• Page Generation 0.06348 seconds
• Memory Usage 9,032KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (50):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete