Excel 2007 : Look up formula

[CCSFM Excel2007]

I have a large spreadsheet with site specific information on multiple buildings within the same site.

Each piece of equipment has a unique asset number eg 0123-456-789-001(all contained in a single cell).

Each number means something different 0123(site)-456(building number)-789(asset description)-001(equipment number)

I need to create a formula to count the number of buildings within the site.

For example:

Asset Numbers:
0123-456-789-001
0123-654-789-001
0123-123-789-001

If this was the data I was analysing I want to look at the second group of numbers(being the building number) to access how many building there are. In this case there is three different buildings and that is the outcome I need from the formula.

Any help would be appreciated.

[dilipandey]

Hi CCSFM,

Welcome to the forum.

See the attached file:-
Asset Number.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[CCSFM Excel2007]

Thanks but the formula wont work because your example has the numbers in separate columns. Each asset number I have is in a single cell eg. Cell A1 = 0123-456-789-001.

[Fotis1991]

Hi

A possible(As we have no sample of your workbook), maybe is this, using 2 helpers & hidden columns.

In B1 and copy down.

=MID(A,6,3)

In C1 and copy down.

=COUNTIF($B1:$B$10,B1)

Then get your result, using this.

=COUNTIF($C$1:$C$10,1)

Is this, works for you?

[SCLai]

If you don't mind adding an extra column, you can use this to obtain the Building Number, then use DILIPandey's array formula to count no. of unique entries.
Given data is in A1:
=MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

Hope this helps!

SC

[CCSFM Excel2007]

Thankyou Fotis, this appears to work but due to the size of my spreadsheet (3500 entries) I was hoping there is a way to achieve this with maybe one formula?