Complicated Formula

ScottG

New Member
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...

Anyone have any ideas? Thanks in advance!

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Can't you put eg:

=LEFT(A1,6)

in a spare column and use that?

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...

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?

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.

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

Works perfectly.

Thanks a bunch!

Replies
8
Views
569
Replies
65
Views
5K
Replies
0
Views
860
Replies
3
Views
497
Replies
2
Views
758

1,219,993
Messages
6,151,354
Members
451,021
Latest member
alejandrolima

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.

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

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