Finding most common occurence of values in cells containing letters and numbers

  • Thread starter Thread starter sparklyballs
  • Start date Start date
S

sparklyballs

i have to do a shortage analysis of various part numbers at work.
i do this on a weekly basis , putting each weeks new data in a new
worksheet.
i need to be able to find the most common occuring part no. across
these worksheets.
the problem i have that these part numbers have various different
formats
some are just plain numbers eg 1234
some have a stroke and a number after them to denote a left and right
pair of the same part eg 1234/1.
the last type has the stroke for left and right, and an additional
stroke and a letter to denote revision changes to parts eg 1234/1/A
the part numbers are anywhere from 2 digits up to 7 digits.
is there a way to find which part occurs most so that i can then inform
the right people that they need to concentrate on the particular part.
the part numbers are always in the same column on each worksheet.
 
Hello Sparklyballs,

If I understand you correctly, you need to extract the first numbers which
in your case is "1234" from the three different formats (1234, 1234/1 and
1234/1/A).

I propose you use the following formula in an adjacent column:

=VALUE(IF(ISERROR(FIND("/";A2));A2;LEFT(A2;FIND("/";A2)-1)))

This formula first finds out if cell A2 (the cell of your first original
part number) contains any "/", otherwise it pastes the value in cell A2. If
cell A2 does contain a "/", it extracts all the digits before that sign. Fill
down this formula to include all original part numbers and you should have in
the case you described 1234 in all cells in the new column.

All you need to do now is create a pivot table and make sure you count the
Parts and you get a nice summary of how many parts is in your worksheet.
There is a nice guid of how to create a pivot table if you choose 'DATA >
Pivot table or Pivot table reports...' in the menu.

Hope this was what you were looking for.

Johan

"sparklyballs" skrev:
 
Hello Sparklyballs,

If I understand you correctly, you need to extract the first numbers which
in your case is "1234" from the three different formats (1234, 1234/1 and
1234/1/A).

I propose you use the following formula in an adjacent column:

=VALUE(IF(ISERROR(FIND("/";A2));A2;LEFT(A2;FIND("/";A2)-1)))

This formula first finds out if cell A2 (the cell of your first original
part number) contains any "/", otherwise it pastes the value in cell A2. If
cell A2 does contain a "/", it extracts all the digits before that sign. Fill
down this formula to include all original part numbers and you should have in
the case you described 1234 in all cells in the new column.

All you need to do now is create a pivot table and make sure you count the
Parts and you get a nice summary of how many parts is in your worksheet.
There is a nice guid of how to create a pivot table if you choose 'DATA >
Pivot table or Pivot table reports...' in the menu.

Hope this was what you were looking for.

Johan

"sparklyballs" skrev:
 
It sounds to me that you need a table with all possible variations of part
numbers in one column and a corresponding column of equivalent part numbers.
You need something like this if part A rev A can be replaced by part a rev B
or part A rev C, but part A rev C cannot be replaced by prior versions. They
have to be treated as different parts. I doubt that part A left can be
replaced by part A right.

It also sounds to me like you have a database app on a spreadsheet. This may
be the best solution, but if the business is growing this could bite you.

A lot of "Excel problems" are really business problems, they just ain't
doing it right.


"sparklyballs" <[email protected]>
wrote in message
news:[email protected]...
 
It sounds to me that you need a table with all possible variations of part
numbers in one column and a corresponding column of equivalent part numbers.
You need something like this if part A rev A can be replaced by part a rev B
or part A rev C, but part A rev C cannot be replaced by prior versions. They
have to be treated as different parts. I doubt that part A left can be
replaced by part A right.

It also sounds to me like you have a database app on a spreadsheet. This may
be the best solution, but if the business is growing this could bite you.

A lot of "Excel problems" are really business problems, they just ain't
doing it right.


"sparklyballs" <[email protected]>
wrote in message
news:[email protected]...
 
Back
Top