Complicated Formula

ScottG

New Member
Joined
Sep 14, 2006
Messages
4
Ok, I'll explain this as best I can. I've been able to work out part of my solution but not all of it.

I've got a column of data that is regularly updated, which looks something like:

REMOTES
H00001AA.SNT*
H00001AB.SNT*
H00001AC.SNT*
H00001AE.SNT*
H00001AF.SNT*
H00006AB.SNT
H00011AA.SNT
H00018AA.SNT
H00024AA.FOX
H00026AA.SNT
H00026AB.SNT
H00028AA.SNT


Now, what I need to do is generate a listing or report that will go through this column and find how many unique occurences there are. BUT, here's the catch, I only need to match the first 6 characters of each cell.

Is there a way to do this? I can report the unique occurences both through a formula and through a pivot table, but I can't seem to figure out the filter part for the first 6 characters...

:cry:

Anyone have any ideas? Thanks in advance!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ScottG

New Member
Joined
Sep 14, 2006
Messages
4
Well, I thought about that. But it would most likely confuse the user. I was hoping there was a way I could do all the calculations behind the scenes where the user wouldn't see it.

But, if it comes down to that, I could place the calculation way off screen in a column far down so the user won't see the data. But how would that affect the data when it's constantly changing. If rows/cells are emptied, would the calculations still be accurate?

Hmm... Not sure if any of that made sense... :)
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=SUMPRODUCT((A1:A13<>"")/COUNTIF(A1:A13,LEFT(A1:A13,6)&"*"))

to get the unique count. Or do you need to present the unique list too?
 

ScottG

New Member
Joined
Sep 14, 2006
Messages
4

ADVERTISEMENT

Yeah, they are wanting to see the unique name and the count. I figured I could do 2 seperate columns, one for the name and one for the count.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Assuming your list starts in A2, going down.

Try:

=LEFT(INDEX(A2:$A$100,MATCH(0,--ISNUMBER(MATCH(LEFT(A2:$A$100,6),$E$1:E1,0)),0)),6)

confirmed with Ctrl + shift + enter in E2, then dragged down
Book1
ABCDE
1
2H00001AA.SNT*H00001
3H00001AB.SNT*H00006
4H00001AC.SNT*H00011
5H00001AE.SNT*H00018
6H00001AF.SNT*H00024
7H00006AB.SNTH00026
8H00011AA.SNTH00028
9H00018AA.SNT
10H00024AA.FOX
11H00026AA.SNT
12H00026AB.SNT
13H00028AA.SNT
Sheet1
 

Forum statistics

Threads
1,141,913
Messages
5,709,298
Members
421,625
Latest member
renrut_5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top