Home

Get the number of times of text is repeated in some range in several sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-276563%22%20slang%3D%22en-US%22%3EGet%20the%20number%20of%20times%20of%20text%20is%20repeated%20in%20some%20range%20in%20several%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20several%20sheets.%3C%2FP%3E%3CP%3EIn%20the%20range%20from%20%3CSTRONG%3EB20%3AB25%3C%2FSTRONG%3E%20I%20have%20some%20values%20from%20a%20drop-down%20list.%20In%20the%20left%20column%20when%20that%20values%20are%20wrote%2C%20the%20workbook%20automatically%20has%20to%20set%20the%20many%20times%20of%20that%20value%20has%20selected%20in%20all%20sheets%20simultaneously.%3C%2FP%3E%3CP%3EFor%20example%20if%20in%20the%20sheet%202%20I%20write%20the%20text%20%22TEXT%22%20in%20cell%20B22%2C%20the%20workbook%20has%20to%20look%20up%20in%20the%20last%20sheets%20if%20the%20text%20%22TEXT%22%20exists%20ONLY%20at%20range%20B20%3AB25.%20If%20this%20text%20was%20found%2C%20so%20the%20workbook%20has%20to%20add%201%20to%20the%20left%20cell%20of%20value%20%22TEXT%22%20of%20the%20last%20sheet%20when%20the%20%22TEXT%22%20was%20wrote.%3C%2FP%3E%3CP%3EThe%20workbook%20can%20have%20to%2060%20sheets.%20And%20for%20example%20the%20%22TEXT%22%20could%20be%20wrote%20at%20first%20time%20in%20the%20sheet%2055%2C%20and%20its%20left%20number%20must%20be%201.%20If%20the%20%22TEXT%22%20is%20write%20again%20in%20the%20sheet%2056%2C%20the%20value%20at%20its%20left%20must%20be%202.%3C%2FP%3E%3CP%3ESo%20the%20column%20A%20must%20be%20a%20counter%20of%20times%20that%20value%20in%20column%20B%20is%20wrote.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20163px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F57960i95BE906252FDEAD3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22TFtgr%22%20title%3D%22TFtgr%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESHEET%201%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20159px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F57961iF19081B9AFE8A62E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22tSZSg%22%20title%3D%22tSZSg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESHEET%202%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20153px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F57962i616E0CF2C6AA8954%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22GS9qL%22%20title%3D%22GS9qL%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESHEET%203%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20with%20CELL%2C%20ADDRESS%20and%20MATCH%20but%20I%20had%20not%20lucky.%3C%2FP%3E%3CP%3EDoes%20exists%20in%20VBA%20a%20formula%20that%20I%20can%20create%20something%20to%20count%20such%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-276563%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Daniel Baharian
New Contributor

I have a workbook with several sheets.

In the range from B20:B25 I have some values from a drop-down list. In the left column when that values are wrote, the workbook automatically has to set the many times of that value has selected in all sheets simultaneously.

For example if in the sheet 2 I write the text "TEXT" in cell B22, the workbook has to look up in the last sheets if the text "TEXT" exists ONLY at range B20:B25. If this text was found, so the workbook has to add 1 to the left cell of value "TEXT" of the last sheet when the "TEXT" was wrote.

The workbook can have to 60 sheets. And for example the "TEXT" could be wrote at first time in the sheet 55, and its left number must be 1. If the "TEXT" is write again in the sheet 56, the value at its left must be 2.

So the column A must be a counter of times that value in column B is wrote.

TFtgrSHEET 1

 

tSZSgSHEET 2

 

GS9qLSHEET 3

 

 

I tried with CELL, ADDRESS and MATCH but I had not lucky.

Does exists in VBA a formula that I can create something to count such this?

 

Thanks.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies