I'm trying to run a script from a CAD software which exports property values into an Excel spreadsheet. I need to then sort the spreadsheet by part numbers (which are located in column G) but my script is not working correctly. My data range is A1:G50.
Const xlCenter = -4108
Const xlAscending = 1
Const xlYes = 1
Const xlSortOnValues = 0
[Code] ............
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
how can i sort a text column by the last words after the "-" automatically. For example, this is a text column:
1. 12634 - Sweetare Dr. - Garden Grove
2. 15234 - Hotflame Dr. - Irvine
3. 16612 - Arigato - Japan
4. 22634 - Saigon - Garden Grove
I need to sort the column by the word(s) after the '-' sign.
I have data with two types of records: those with "orders" in teh comments field and those with something else in that field. I'm able to sort on comments so "orders" records appear at the bottom, and insert two blank rows, separating the two datasets. Now I have to be able to set the range of the top portion. I worked on the problem, and used some of the code in the response to my post. I need to store two cell addresses.
To determine the range, I find the first blank column, then move one cell to the left, then I need to store that column's letter. AFter I find the first row with "orders" in the comments, I need to insert two blank rows and move back up to the row above that containing data (this much I've done), then store that cell's row's address. I need to use the column and row I record as the range to select for the sort. So, I basically need to things:
1. How to store the active cell's column and row
2. How to stop recording, type in code, then resume recording.
i have a list of part numbers that all have prefixes and i need to sort them into regular part number order and can't seem to figure out why i'm having such a hard time. the order i need them is this example. i need after part number 1-1 to be 1-2 followed by 1-3 etc. not 1-10 followed by 1-100. i hope i'm explaining this correctly.
View 9 Replies View RelatedUsing the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
How can you sort item numbers, targeting only the numbers in the mock up file?
Example:
item number: GW14SO0000003
item company: GW
item location: 14SO
item counter: 0000003
How can I only sort the item by the last 7 digits "0000003"
I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)
Code:
Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation
[Code]....
This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.
I am trying to do a VLOOKUP on a worksheet with a list of our Part numbers. The Part numbers begin with zero and go into the alphabet with anywhere from 3 digits upto 18.
When I sort the sheet Excel sorts the numeric by the number of digits in the number AND totally ignores the first zero. I can not format as numbers since again Excel drops the first zero. Therefore, when I do the VLOOKUP it will not look through the entire numeric list for the higher digit numbers. Is there anyway to resolve my VLOOKUP issue with indexing or?
I am trying to find a way to eliminate endings off of part numbers.
Below are the list of endings that I need to be eliminated from an extremely long list of variant part numbert. This list below may need to have additional endings added, but nothing will be removed. Please see attachment for example of part numbers that need below endings eliminated. Thanks!
R
T
G4
E4
RG4
RE4
TG4
TE4
G6
E6
RG6
RE6
TG6
TE6
/2K5
/3K
/250
/500
I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result.
Here's how the Data looks like:
OrderPart numbers
123A
123B
123C
123D
234A
234B
234E
346A
346C
Answer for above would be 2 orders that have A and B part numbers on the same order.
Im using a formula to identify new part numbers. The formula is: =IF(ISNUMBER(MATCH(A217,Existing!A:A,0)),"","NEW"). However you can clearly see from the attached that if has flagged a duplicate part number as new. Why would it do that? Check out A1368 in existing and A217 in new.
View 5 Replies View RelatedI have a column of part numbers in the format 530020109 but I would like to change to 530 0201 09.
View 4 Replies View RelatedI have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08
when I want it to sort like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08
The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.
At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5
I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.
I tried creating a custom list, but there are just too many varieties to list them all.
I need to remove duplicate Part Numbers where other information in the cells will not match. In the following two examples, the only difference is that the COMP_ID: number is different, but for my purposes, the second example is a duplicate and needs to be removed. I have hundreds of rows of this type of information with various part numbers: ...
View 9 Replies View RelatedI have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?
Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W
I have a list of part numbers and a new drawing number and old drawing number. I'm am trying to put this list on the second page of a workbook and write a code on thie first page that allows a person to enter the part number and the old and new drawing numbers will be displayed.
View 3 Replies View RelatedI have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices.
I have one part number in Cell say A1, Its price in Cell B1
i have another same part number in A2, its price in Cell B2
(The macro has grouped them together)
Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers)
This process is to be repeted in the entire worksheet.
Sub InsertRow_At_Change()
'part number
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 1).Value Cells(X - 1, 1).Value Then
If Cells(X, 1).Value "" Then
If Cells(X - 1, 1).Value "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub
I'd like to create a macro to display the top 5 most frequently occurring repairs on sheet 3 of my workbook. I'd like the macro to analyse all the sheets in my workbook except for the first 3 and then output the results on the 3rd sheet from T50 onward. I have attached a template as an example of how all the sheets in the workbook look (excluding the first 3).
All the sheets have the same layout as the "Survey Template" worksheet.The repairs are located under the Log Book Review of Historical Structural Repair in the Survey Template worksheet and all the repairs are picked through a drop down list that is based on a table of repairs listed in the Parts and Prices sheet within the workbook. I have assigned random serial numbers to the repairs (if that makes it any easier to code instead of locating exact string matches).
ExampleSurveyTemplate.xlsx
I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.
PART
QTY
CONSOLIDATED PARTS
CONSOLIDATED
QUANTITIES
I have a Excel 2007 spreadsheet of part numbers and quantities sold. In the spreadsheet we have similar part numbers, but my sumif command is adding these together. the parts are :
0124225031
R124225031
My column of part numbers is formatted as text
My formula is this =SUMIF(Sheet1!H:H,A16,Sheet1!Q:Q) where H is the part number and Q is the quanity
I tried adding a format command in the sumif command, but it returned a 0. =SUMIF(Sheet1!H:H,format(A16,"0"),Sheet1!Q:Q)
better formula and why is excel adding different part numbers together?
i have a spreadsheet with 21,000 part numbers. I am trying to group the like part numbers, then leave a space between the unlike part numbers. right now my spreadsheet has a space between each part number and i want to eliminate that. but also keeping the part's qty, date, etc. with it.
View 14 Replies View RelatedI am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)
View 2 Replies View RelatedI have 3 columns; A:Name, B:Debit & C:Credit. There are multiple row (approx 15,000) where I have a customer name and then a debit at one date, and a credit at another date. There is a debit to match every credit.
I would like to some how sort the sheet so that I can make sure each credit matched the debit. That way I can find what debit's don't match the credits. This is an example of how this looks. http://i303.photobucket.com/albums/n...kejoe/sort.jpg. I would like to sort it like the example on the far right (see attached picture)
I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.
Example Excel sort order:
06090
10400
28198
34078
43321
0BKV9
1C7K4
2P776
3BTW3
4U744
Desired sort order:
06090
0BKV9
10400
1C7K4
28198
2P776
34078
3BTW3
43321
4U744
I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.
I have a 1x20 array with the numbers 1 thru 20 inside. I want a piece of vba code to randomly sort them. I will do this in a loop to create different "starting arrays" for an optimization code. I just need to be able to randomize the beginning. How is this done? I cant have duplicates.
View 1 Replies View RelatedI have data and i would like to number duplicates next to the data so that i can append the cells that are duplicates easily. The data i have is in the following cell format -
Red
Red
Red
Green
Green
Green
Green
Blue
Yellow
Yellow
What i would like is to output serial next to the cells based on the cell uniqueness as in -
Red 1
Red 2
Red 3
Green 1
Green 2
Green 3
Green 4
Blue 1
Yellow 1
Yellow 2
I have a list I want to sort containing both letters and numbers. Right now the regular sort sorts like this
ABC-1
ABC-11
ABC-12
ABC-2
I want it to sort like this and don't have a clue how to do it
ABC-1
ABC-2
ABC-11
ABC-12
I am using excel 2003
I have data in column C of rows 25 to 45. The data varies in number form.
What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.