Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel Query - count number of times a word is in a grid
#1
Hi all

I have an Excel query, if anyone could help me with it, I would be much obliged.

I have a set of initials in a grid:

AA AB AC
AA AB AC
AA AB AC
AA AB AC
AA AB AC
AA AB AC
AV AN AC

I want to use an Excel formula to give a report of the number
of instances of each entry, for example:

AA 5

AB 5

AC 6

AN 1

AV 1

If I put the data into a single column, I can do this with a pivot table.
But there is a lot of data either side of the entries, also it takes
ages to format the each column into a single column that is suitable for
pivot table work.

I'd like to be able to select the data and use a formula to do the counting.

Does anyone know a formula that will do this?

Thanks

Barney
Reply

#2
Hi Barney,

What you're asking is going to be for someone who works in Excel and specialises in formulas.

example: =CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,CODE(LEFT(R[-1]C,1))+1,CODE(LEFT(R[-1]C,1))))&CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,65,CODE(RIGHT(R[-1]C,1))+1)) 

I would try here unless there is someone who works with complex formulas for Excel.

Sorry not my area of expertise 
<left><form action="https://www.paypal.com/cgi-bin/webscr" method="post">If you are satisfied with my help, consider a donation. Thank you so much for your continued support! 
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="Y4ZDLXGFS4F8Q">
<input type="image" src="https://www.paypalobjects.com/en_US/GB/i/btn/btn_donateCC_LG.gif" border="0" name="submit" alt="PayPal — The safer, easier way to pay online.">
<img alt="" border="0" src="https://www.paypalobjects.com/en_GB/i/scr/pixel.gif" width="0" height="0">
</form>

   </div></left> 
Reply

#3
I am guess but I think you want to do something like this



Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

Powered By MyBB, © 2002-2024 Melroy van den Berg.