Excel Formula Help Part III - Final Question

[Ramzavail]

I am looking for a formula that can use a range of misc. random numbers and count up with a "count"

for instance, if I have a range of these numbers
13056
13026
13055
13055
13055
13055
9734
9734
9734
13340
13340
13337
13335
13335

I would want the formula to come up with = 7. Because even though there are multiple lines with the same number, I would want it to count as 1.

Also, I would like for it to be used in an entire range, not just a specific cell or anything like that. For instance, if that range was in column D - I want it to be for D1 : D14 (since there are 14 misc. numbers in this example).

Lastly, it will be sorted together, so another "13340" wouldn't be 50 lines further down for instance, all the "13340"'s would be together. Not sure it that would even matter, but I thought I'd add it.

[RPI-Fan]

Easiest to do this with a VBA script... is that an option?

[Ramzavail]

Is there any reason why it couldn't be an option? You can use it in Excel right?

Not being snotty - just trying to understand.

[headtrauma]

You could use a pivot table.

[Ramzavail]

Id rather not use a pivot table. Only b/c I want to reuse this formula in multiple places and just refill the data and have the formula be useful in each instance without having the re-pivot table.

[RPI-Fan]

Formulas are a little more dynamic, but there's no reason not do to a script.

Something like this:

i = 1 //also feel free to use a cell reference for the starting value of i
Count = 0
LoopCheck = 0

Do

Temp = cells(i,4).value //Change the "4" to whatever column you like, or use a dynamic cell input
Temp1 = cells(i+1,4).value //see above comment
if Temp != Temp1 then
  Count = Count + 1
end if

i = i + 1

if Temp1 = "" then
  LoopCheck = 1
end if

loop while LoopCheck = 0

cells(1,1).value = Count

Not 100% sure this will work, but it is close...

[Ramzavail]

I think you lost me.

[RPI-Fan]

Just go to "View... Toolbars... Control Toolbox", then draw a button anywhere on your sheet. Double-click on the button and paste in that code and it will output the info you want (from Column D) into cell A1.

[Buccaneer]

umm, you could use a simple formula instead, I think

=SUM(IF(FREQUENCY(E1:E10,E1:E10)>0,1))

[Ramzavail]

i = 1
Count = 0
LoopCheck = 0

Do

Temp = Cells(i, p).Value
Temp1 = Cells(i + 1, p).Value
if Temp ! = Temp1 then
Count = Count + 1
End If

i = i + 1

If Temp1 = "" Then
LoopCheck = 1
End If

Loop While LoopCheck = 0

Cells(1, 1).Value = Count
End Sub

[Ramzavail]

thanks Bucc, looks like that worked.

[Buccaneer]

Cool, I do Sum If/IIf stuff a lot in both Excel and Access.

[RPI-Fan]

Great, I always prefer formulae to scripts.

[Ramzavail]

Ok now that I have the set formula, and lets say its for 14 rows - as discussed above - now what if I copy in 25 rows worth of information, I understand the formula will increase the range as I add rows - but is there a way Excel can add the additional 11 rows while I copy and paste the 25 rows in?

Or do I have to count my rows (this case 11 additional), add the rows needed and then copy and paste?

Just wondering if theres a simple way Excel can do it all in one swoop.

[RPI-Fan]

I think if you copy the rows from another worksheet, and then right click somewhere in your list of rows and do "Insert Copied Cells" it will automatically update the formula to include the inserted rows. Not sure if it will work if you append the rows at the end, but if you put them in the middle surely it will update.

[Ramzavail]

yes they sorta worked...I can work with that. thanks.

[Ramzavail]

Is there a VBA script that I can run that can populate all the information in X amount of tabs and populate it all into one tab?

For instance, if I have 25 tabs with 25 lines in each, can I create a button in the 26th tab and have it populate it with the 625 lines from those 25 tabs in one swoop?

[Ramzavail]

bumpus maximus