I have a spreadsheet with only one column named product catergory (UNSPSC if someone is familiar with that).
e.g. I have these 5 numbers for my 5 products:
42121510
42121510
42121510
42121511
42121509
I want to evaluate from left to right where the numbers match and end up with a result, that says 421215 which is what they have in common. I could also have 5 different categories starting with different number e.g. 42, 43, 10 or whatever.
How to do it?
Last edited by morsoe; 07-28-2011 at 03:34 AM.
ok, i have had a look and cant figure it out yet. But something that may help is that you only need to compare the maximum and minimum values in your list of numbers
I put your five numers in cells A1:A5
And then this in your result cell
confirm formula with ctrl+shift+enter
=LEFT(MIN(A1:A5),MAX(IF(LEFT(MIN(A1:A5),{1,2,3,4,5,6,7,8})=LEFT(MAX(A1:A5),{1,2,3,4,5,6,7,8}),{1,2,3,4,5,6,7,8},{0,0,0,0,0,0,0,0})))
Thank you Cookstein2 - it solved my problem :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
morsoe 
Register To Reply
Bookmarks