Trying to get a list of repeated values and their frequency..

[emelen]

Hi All,


This is driving me nuts and any help or advice would be greatly appreciated because I have been utterly frustrated all day with this..


I am simply trying to generate a list of values and how often they repeat from two columns. The columns are disjointed, the first set of values runs from B33:B296 then continues at F8:F31. I would like to generate a list of the values which repeat and how often they repeat.. This initially seems simple enough but unfotunately i don't live in MS's shiney happy world where data sources are perfect ranges or tables.. :-(


I have tried searching the internet all day.. I have tried the suggested pivot tables and advanced filters, i have watched videos and read forums, inc. this one but whatever i try i seem to generate either errors due to an inability to select either the multiple columns or named ranges, or a pivot table which gives me almost what i want but will only let me select the first column.. I've tried using the 'consolidated data' option but that gives me an error too.. COUNTIF obvously won't work becuase of the multiple columns.. I am becoming SO frustrated with this..


I'm not particularly fluent in Excel but I don't understand why this appears to be so difficult to me.. Any help or suggestins would be greatly appreciated..


Many thanks,


matt..

 

[SirJB7]

Hi, emelen!


If it's a once job try doing this:

a) in an aux column, for example I, type in I8: =F8

b) copy down to I9:I31

c) in I32 type: =B33

d) copy down to I33:I295


There you have one unique range from I8:I295. Proceed with the COUNTIF solution.


Regards!

 

[Kyle McGhee]

Hi emelen,


You should just be able to do this, unless I am misreading your request:

enter this into C33 and copy down to C296

=COUNTIF($B$33:$B$296,B33))+ COUNTIF($F$8:$F$31,B33)


enter the same formula in G8 except change the =B33 to =F8 and drag down to G31

 

[Kyle McGhee]

edit:


Have you tried using the "multiple consolidation ranges" method of a pivot table?


With your data, put a 1 in cells C33:C296, next to your data in B and the same in G ,next to the data in F. Put a header above the data, Something like Data in B33 and Count in C33. Put the same headers in F7 & G7.


-Now, assuming you have XL2007, press Alt, then D then P. This brings up the pivot table wizard.

-Select "Multiple Consolidation Ranges" and "Pivot Table" and press next.

-Select "create a single page field for me" and press next

-Select B32:C296 and press add, then select F7:G31 and press add

-Click Finish

-Filter the pivot table for anything greater than 1

 

[emelen]

SirJB7 and Kyle,


Many thanks for your replies and suggestions and more importantly, the time you took to both read, analyse my problem and produce potential solutions with the limited info I supplied. I truly appreciate it. Thanks to your collective insight I believe I have now found a resolution to my problem.


Again, I thank you both.


Best regards,


matt..