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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,057
Messages
5,545,755
Members
410,704
Latest member
Cobber2008
Top