I have so many part codes in column A with their Group in Column B.I want to arrange in ascending orders only those part codes which have almost matching in Column A with other part codes with different group.Please see the attached file and expected result.
View 4 RepliesMy question is how many parts havinig end with C?Also arrange these parts in ascending order in a new column. Refer to the attached file and expected result also.
View 5 Replies View Relatedi have a huge data in excel some rows has been shifted down ...e.g. from column.AY3200 data shifted down :-in column B3201 & i want arranged that B3201 in AY3200,so .it;s seem like below ..
sssssssssssssssss
ddddddddddsssssssssssssssssss
i got data something like this...
m4-1
m4-10
m4-100
m4-2
m4-3
m4-30
i try to arrange it in ascending order...
m4-1
m4-2
m4-3
m4-10
m4-30
m4-100
This is for a race track. Drivers sign in on one sheet. On that sheet there is 4 different columns. Column 1 lists the division that they race in. Column 2 is the name of the driver. Column 3 is his car #. Column 4 is the number that he draws that decides his starting position. Typically there is 2 heat races per division with the odd number that is drawn being in the first heat and the even number being in the second heat. I created a second sheet called heat races. I am using an index function with a small function with a row. I have my divisions listed separately with 2 heats per division. I want to display the car number per division but lined up by the position that they draw. I have figured out how to display all of the cars per division, but I can't figure out how to separate them by what they draw (odd and even and in order from lowest to highest).
View 1 Replies View RelatedHere I have some samples of data from row A1:V1 that I want to arrange in ascending order through A4:V4.
For that I’m using the formula below in cell A4 across V4
=SMALL($B$2:$W$2,COUNTA(B2:$W2)-(COUNTA(B2:$W2)-COLUMN(A2)))
Sample data
ABCDEFGHIJKLMNOPQRSTUV11230507801011121314150170192002523400000012357810111213141517192025
But I want to arrange them leaving zeros as shown below in A4:V4
ABCDEFGHIJKLMNOPQRSTUV11230507801011121314150170192002523412357810111213141517192025
In Column B I have dates and in column C I have values correponding to the dates in B.
In Column J I have dates and in column L I have values correponding to the dates in J.
The first date entry in column B and J are equal but after that it is different based on a workday formula adding months, years, days etc. etc.
B31 = 06-apr-09 and J31 = 06-apr-09
B32 = 06-apr-10 and J32 = 06-Oct-09
B33 = 06-apr-11 and J33 = 06-apr-10
B34 = 10-apr-12 and J34 = 06-Oct-10
B35 = 08-apr-13 and J35 = 06-apr-11
B36 = 07-apr-14 and J36 = 06-Oct-11
J37 = 10-apr-12
J38 = 08-Oct-12
J39 = 08-apr-13
J40 = 07-Oct-13
J41 = 07-apr-14
As may notice column B adds 1 year to the previous date while column J adds 6 months to the previous date. (This is changing and B can add 1 month while J adds 3 months etc. etc.
I need a formula in column N that will look at the dates in column B and in column J and list them in column N in ascending order while not taking into account double entries.
I have built a reporting dashboard to track emplyee efficiency .Within this dashboard i have produced a point scoring league table . I am lookinf for a forula that will automatically work out who my top performers are and arrange them in them in the form of 1st , 2nd ,3rd etc . I believe the answer to lie in the form of a pivot table , perhaps with a macro to populate the data , but would be grateful for come direction
View 3 Replies View RelatedTrying to arrange numerical values in order from lowest to highest in a column i have a code to do that but i need it to be able to but to a range in the column so it only sorts the values in rows 3 - 60 not all of the rows in the column
Sub Testsort()
Range("A3").CurrentRegion.Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I am trying to match up company codes across three different columns. I am inserting rows to get them to match up. However, there has to be an easier way.
1017
1017
1027 Total
1051
1021
[Code] ...
my w/s has a column for codes and then currency columns. Users may select codes from a list of over 600 codes. I need to enter a formula that subtotals by each code used.
table looks like this:
CODE 1Q$ 2Q$ 3Q$ 4Q$ SUBTOTAL ADJ ADJ ADJ TOTAL
8810 10000 10000 10000 10000 40000 50000 45000
8910 10000 10000 10000 10000 40000 50000 45000
8810 10000 10000 10000 10000 40000 50000 45000
8811 10000 10000 10000 10000 40000 50000 45000
so in this example I need subtotals for 8810, 8910 & 8811
not sure if I need to use a pivot table, an array formula or if statement.
I have a large sheet of data I'm trying to sort through. Each row of data has a column that contains a long string description. Each of these strings contains a three letter codes in all caps that I want to be able to pull out. I have a seperate sheet that has all of the possible three letter codes in one column. Is there a formula that can compare each string of text to this list of three letter codes and if it find a match put that code in a column on the original sheet?
Doc Number
String
Code
33333
This is an example string of text. An example three letter code would be HCB. The location of the code within the string varies.
HCB
33332
This is an example string of text. An example three letter code would be CDQ. The location of the code within the string varies.
CDQ
33331
This is an example string of text. An example three letter code would be RCA. The location of the code within the string varies.
RCA
Codes
DCP
HCB
RCA
CDQ
LCP
DCA
SCR
I am working on a large volume spreadsheet comprising of the usual mailing list fields such as first name, surname, company, address1, address 2 , suburb, state, postcode etc etc
Is there a way for Excel to auto fill a line of details (or display a list the matching entries)?
this way, i can start to enter a semi-unique field such as surname, company or address and a list of matching entries pop up for me to select the best fit.
I then would fill in the remaining data.
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 RelatedColA
there was a dog that jumped over
there was a dog that jumped over the fence
Everything is in ColA. We are on excel 2007. We need to highlight these two are duplicates based on matching the first 40 characters.
How do I create a formula that will count the occurrences on each row within the range F7:H106 where the single digit in FJ6 is paired with the 2 digit value in FF7. Match in any order.
example
If FF7=00 and FJ6=1 (Match 00 with 1 in any order)
f7:h7=010, match, count 1
f8:H8=059, no match
f9:h9=100, match, count 1
f10:h10=001, match, count 1
f11:h11=007, no match
Result=count=3
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group (from a drop down) which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. There is no fixed number of inputs for any one group i-e the solution has to be dynamic.
Attached is a file illustrating the inputs and the required output. I would prefer a formula based approach but not against VBA based suggestions either.
Sample_file_summary.xlsx
Also posted at: [URL] ....
Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem
View 11 Replies View RelatedI'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
Basicalley I have a series of columns, each 5 letters together represent an order(a,b,c,d,e), the columns are repeated approximately 15 times, similar to the following;
Bag Size:B/G5lb2lb 16oz8ozB/G 5lb 2lb (repeat...)
Catagory 1ABCDEA1 B1 C1 (repeat...)
Catagory 2 AABBCCDDEEAA1 BB1 CC1 (repeat...)
(...Repeat)
The B/G stands for Whole Bean, or Ground, the A's are drop down lists giving those 2 options, the other cells (non-A's) are all numeric. At the end of each row, the orders need to be summed up into categories; 5lb whole, 5lb ground, 2lbs whole, 2lbs ground, 16 whole, 16 ground etc..
I am looking for a way to sum each category for all orders as necessary, in one cell adding all B's (in that row for that catagory) if its corresponding A is whole bean, in the next summing all b's if its corresponding A is Ground. The same will go for the C's D's and E's.
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 looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).
=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I have an excel document with a list of about 12,000 names/addresses. They are currently displayed like this:
A B C
1 <name1> <name2> <name3>
2 <address1> <address2> <address3>
3 <zip1> <zip2> <zip3>
...and I want to re-arrange them so they are displayed horizontally:
A B C
1 <name1> <address1> <zip1>
2 <name2> <address2> <zip2>
3 <name3> <address3> <zip3>
Is there any possible way of doing this quickly without copying/pasting 12,000 lines of text?
I want to delete alternative row in a worksheet or sorted so that I only have the following arrangements..
I have Data in Rows which as followings
Row 1
Row 2
Row 3
Row 4
Row 5
.
.
so on till 1000 data
I don't want Row 1, 3, 5 .... so on
The result should should be arranged as follows:
Row2
Row4
Row6
.
.
.
so on
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.
Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
How to arrange a lookup formula.
Book2.xlsx
In this example, I am attempting to look up one value based on two criteria. The month must match, and the location must match. In doing so, the value "1" should be returned in the example.