Sorting information?

C

Cozmo35

Board Regular
Joined
May 29, 2009
Messages
61
This is my second posting. You guys helped me out of a REALLY tight jam last time and I appreciate it like you'll never know.

I have another question. I have a list of repairs that my company has done (32000 + items). There are several part #'s entered by several different people. Each person has added his own suffix on the end of the part #. I need a way to sort these part #'s that contain the same PREfix and also determine the avareage cost for them.

I know how this can be done if I do each one individually, but I am looking for an easier way. I know ya'll can help. :)
 
Sort by date Sort by votes
Do either the part numbers or the suffix have a fixed length? Or, is there some sort of separating character between the part number and the suffix? Posting some sample data would help tremendously.
 
Upvote 0
Timorrill,

Here is an example of the part #'s. As you can see, some have the same part# only a different suffix.

<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=317 border=0><COLGROUP><COL style="WIDTH: 238pt; mso-width-source: userset; mso-width-alt: 11593" width=317><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 238pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" width=317 height=25>0010-09416-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09416-SRC</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09416STI-SRC</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09490W-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09750-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09750-SRC</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-09750W-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-13878-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-20524-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-30686W-SOSP</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-36162-SRC</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>0010-36408-SRC</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>1110-01043-SRC</TD></TR></TBODY></TABLE>
 
Upvote 0
Take a look at this is see if this gets you close to what you need:

Excel Workbook
ABC
1Part Number with SuffixPart NumberCost
20010-09416-SOSP0010-0941622.84
30010-09416-SRC0010-0941629.76
40010-09416 Average26.30
50010-09416STI-SRC0010-09416STI5.68
60010-09416STI Average5.68
70010-09490W-SOSP0010-09490W7.63
80010-09490W Average7.63
90010-09750-SOSP0010-0975017.82
100010-09750-SRC0010-0975021.48
110010-09750 Average19.65
120010-09750W-SOSP0010-09750W18.69
130010-09750W Average18.69
140010-13878-SOSP0010-1387825.07
150010-13878 Average25.07
160010-20524-SOSP0010-2052424.48
170010-20524 Average24.48
180010-30686W-SOSP0010-30686W19.66
190010-30686W Average19.66
200010-36162-SRC0010-3616214.64
210010-36162 Average14.64
220010-36408-SRC0010-3640813.22
230010-36408 Average13.22
241110-01043-SRC1110-0104326.13
251110-01043 Average26.13
26Grand Average19.01
Sheet1


In Column B I am using formulas to get rid of the suffix. The formulas assume that each entry in the table contains two dashes (one dash in the actual part number, one dash immediately preceding the suffix). Then, selecting columns A, B, and C, I created subtotals on the data and selected Average on Cost (column C) where there is a change in Part Number (column B).
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Command buttons not sorting - and sometimes jumping around on sort
2
Replies
10
Views
296
netxtown
N
N
  • Question Question
How can I sort rows into different worksheets?
Replies
2
Views
209
nithchun
N
G
  • Question Question
Curious Filter-by-formula issue
Replies
1
Views
141
gordonpsmith
G
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
576
STARL0RD
S
NateSC
  • Solved
Username and password system to access information
Replies
3
Views
300
Logit
Logit

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top