Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Paste this in D1 and drag down. </p> <pre><code>=IF(A2&lt;&gt;A1,COUNTIF($A$1:$A$100000,A1),"") </code></pre> <p>Adjust the range as you need. This formula assumes that the first 3 digits are in there own cell. </p> <p>If your sample data is all in one column then you will have to use a <code>Sumproduct</code> with a <code>Left</code> function in place of the countif. You can use the following Formula in this case, But if your sample data is in 3 columns definatly use my fast formula. </p> <pre><code>=IF(LEFT(A1,3)&lt;&gt;LEFT(A2,3),SUMPRODUCT(--(LEFT($A$1:$A$100000,3)=LEFT(A1,3))),"") </code></pre> <p><strong><em>EDIT</em></strong> Based on your comments and answer I have made a full guide on using the <code>countif</code> method as VBA should <strong><em>ALWAYS</em></strong> be avoided if possible. You had issues because your sample data provided in your question did not contain headers/ Column Labels here is the fixed guide.</p> <p>Starting with your 3 columns with headers I wqould create a named range on the column youd like the counts for to do this use built in Name Manager and click on new: </p> <p><img src="https://i.stack.imgur.com/sfiSv.jpg" alt="Name Manager New"></p> <p>Then from this Set the Name to <code>CountColumn</code> and in the Formula use the following: </p> <pre><code>=OFFSET($A$2,0,0,COUNTA($A$2:$A$1000000),1) </code></pre> <p><img src="https://i.stack.imgur.com/PzasG.jpg" alt="Name Manager Formula"></p> <p>Now using a modified version of my original answer type the following in cell <code>D2</code>: </p> <pre><code>=IF(A3&lt;&gt;A2,COUNTIF(CountColumn,A2),"") </code></pre> <p><img src="https://i.stack.imgur.com/DtdQz.jpg" alt="Formula"></p> <p>AS shown above this is IDENTICAL to what your original question Asked for in <code>Desired Output</code>. </p> <p>Now to further this to get the highlights as your VBA Code looks to do I would use the following. </p> <p>Go Back to the Name Manager, as we did for the <code>CountColumn</code>, and Create another new Named Range called <code>Sums</code> And then change all the <code>A</code> references to <code>D</code> like follows:</p> <pre><code>=OFFSET($D$2,0,0,COUNTA($D$2:$D$1000000),1) </code></pre> <p><img src="https://i.stack.imgur.com/4FCnX.jpg" alt="SumsNamed"></p> <p>And you Name Manager Should look like the following: </p> <p><img src="https://i.stack.imgur.com/XhgxK.jpg" alt="Name Manager2"></p> <p>Now in the Name Box (top left box next to formula bar) type in the word <code>Sums</code> to select the entire sum area so we can format it: </p> <p><img src="https://i.stack.imgur.com/0zeiA.jpg" alt="Sums"></p> <p>Then ****while <code>sums</code> area is highlighted*** go to Conditional Formatting ~~> New Rule: </p> <p><img src="https://i.stack.imgur.com/h4KhX.jpg" alt="enter image description here"> And use the built in No Blanks Function: </p> <p><img src="https://i.stack.imgur.com/Hpwk7.jpg" alt="NO Blanks"></p> <p>Then for the format Use Fill and the color you want, Based on your posted formula I used the Green Color: </p> <p><img src="https://i.stack.imgur.com/rBmWa.jpg" alt="Fill Green"></p> <p>Now you should be done and your Data should look as the picture below does: </p> <p><img src="https://i.stack.imgur.com/Ba7YD.jpg" alt="Finish"></p>
    singulars
    1. This table or related slice is empty.
    1. POHow do I count occurrences of a pattern of data in Excel?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USuser2140261
    1. USuser2140261
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThe first formula seems to do the trick, but it puts the final count of the occurrrences in the next row, not in the last occurrence row.
      singulars
      1. PO
      1. USKen Ingram
    2. CODid you start in row1?
      singulars
      1. PO
      1. USuser2140261
    3. COI started in row 2, under the labels. It more or less did what I needed. Not perfect, but close enough to be useful for the moment.
      singulars
      1. PO
      1. USKen Ingram
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload