for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

GregP Oct 2018 2:26pm, 30 Oct 2018 108,306 posts	Quote Pin GregP
flanker Oct 2018 2:42pm, 30 Oct 2018 17,941 posts	Quote Pin flanker what was the new title major cunntasair?
Diogenes Oct 2018 3:11pm, 30 Oct 2018 31,386 posts	Quote Pin Diogenes The following ought to be easy in Excel, but I can’t find a way that is quicker than the manual process I use. Each month I have a range of data, generally about 4 columns wide and up to 30 rows. Each cell will contain a value which is one of about 200 possible number/letter combinations, for example 10G or 13AA. One value can appear multiple times. Some cells are blank. I want to find a quick way to list and count each particular value. I thought I’d be able to do it using a pivot table, but the results are messy (and the blanks cells don’t help). I spend longer checking my results than I do using my manual method. (The manual method sees me copy the contents of the range into on column, sorting then counting them.) Any ideas?
GregP Oct 2018 3:16pm, 30 Oct 2018 108,315 posts	Quote Pin GregP column and pivot, I reckon
um Oct 2018 3:26pm, 30 Oct 2018 581 posts	Quote Pin um Dio - are the '200' possible values known? If so, use countif(array,value), eg
um Oct 2018 3:28pm, 30 Oct 2018 582 posts	Quote Pin um ie list all possible values in col A, set col B to the count using fomula and eithe put the data in the same sheet, or different sheet. If values are not known, maybe record a macro of your manual process. Allow for absolute maximum entries - and then just rerun the macro each time
Diogenes Oct 2018 3:31pm, 30 Oct 2018 31,389 posts	Quote Pin Diogenes Um, yes, but I don’t understand your image. The cells containing the values are just a few columns in a much larger workbook
Diogenes Oct 2018 3:31pm, 30 Oct 2018 31,390 posts	Quote Pin Diogenes X-post. Thanks
Diogenes Oct 2018 3:32pm, 30 Oct 2018 31,391 posts	Quote Pin Diogenes A macro was my other thought
um Oct 2018 3:45pm, 30 Oct 2018 583 posts	Quote Pin um Even easier (maybe) - same list of possible values in your col A, then formula is (eg) =COUNTIF([filename.xlsx]Sheetname!X:Z,An) where you need to know filename & sheetname, replace X:Z with the cols you want. Set the first cell, then drag down to replicate by row