0

I have a live datasource from a web page and in one of the columns it has an account code.

In my excel spreadsheet I'm looking at building a list of the account codes in one column and then in the next column showing a number of times the account code has shown up.

I could do this with the existing codes that are on the datasource but there are always new account codes being added, so is this possible to do from an dynamic point of view so any new accounts are automatically added to the list?

Vince P
  • 103

1 Answers1

1

The first stage is to create a column of unique values from your data. Say your account code is below

[COLUMN A]
Account ID  
----------  
A           
B           
A           
D

You can get a list of unique values in the B column using:

=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($B$1:$B1,$A$2:$A$20),0),1),"")

This assumes the maximum number of rows in your list is 20. If you have more, make the number 20 in the above formula bigger wherever it appears. You need to enter it using Control + Shift + Enter as it is an array formula. Fill this formula down as much as is necessary.

Then to get the list of times it appears simply use to following:

=COUNTIF($A$2:$A$20,B2)

Where the $A$2:$A$20 is the array of the original Account_ID data and B2 is a value in the unique list. Again, replace 20 with as big number as necessary and fill down. Both formulas can be referenced across sheets.

ruskind
  • 335