Macro - Combine Quantity Of Item Numbers With 6 Out Of 9 Matching
Apr 16, 2012
I am looking for a macro that will combine qty's of item codes when first 6 out of 9 numbers in the item number match. Please see example of data below. In this case, the macro looking at the first 6 numbers in used and new. I should get a total of 26 - new and 45 -used.
A B C D E
Item Name yard Item Numb Inv New Inv Used
Frami panel 3x6 416-100 588801000 6 27
Frami panel 3x6 416-150 588801000 10
Frami Xlife 3x6 416-100 588801500 20 8
I need a formula that will calculate the quantity of each size of an item (XS,S,M,L,XL). For example to calculate the quantity for Mediums, I need the formula to look at column C, if M is entered, then look at the adjacent cell in column A for the quantity and formulate a total number (from rows 4-21) and enter the total quantity in C27. See attachment.
in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group 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. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
1) I have 2 worksheets that contain 2 matching name columns; " Date" and the primary column A "Symbol". The rest of the column headings in each of the worksheets are different. Data (from internet sources) is inserted into the worksheets in table format on daily basis
Sheet 1 name = Data 1 Sheet 2 name = Data 2 Master worksheet name = MERGED DATA
2) I use the Match and Index formula for both sheets
the following formula is inserted in row 2 of Data 1 and Data 2 sheets (other than Col A "Symbol")
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.
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1 data data data data p2 data data data data
[Code] .....
needs to become:
p1 data data data data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I have to create lists of data in the following format:
123121321,12321215,121351213 (numbers with , with no spaces)
These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above.
The formula I have been given: =INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs"))
currently I only get 10 numbers per row
I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row.
Is there another way to combine up to 500 numbers in one row?
I have two set of numbers placed on two separate sheets of an Excel file. I want to mix them in certain proportions (Proportion changes frequently). Can I do that in Excel if Yes how ?
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:
6201 = Dark blue font, light blue background 6202 = Dark Green font, light green background 6230 = Dark Purple font, light purple background 6245 = ... 6445 = ... 6447 = ... 6448 = ...
and the list goes on for about 200 different colour combinations.
I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this
I think I need a macro to do this. Can I use vlookup function for a range of number? lets say I have number 1001 to 1005. i want to combine the result. any other way other than using this formula
I need a process to combine a set of numbers that have a range of 1 to 48. The set can vary. I need all possible combinations. Example:
out of the range 1 to 48, the set of numbers are (6,11,15,21,22,27,33,34,47). The numbers need to be combined into all possible combinations of subsets of 6 numbers. The criteria for combination is does not include sequencing. In other words the numbers only need to combined into 6 number sets that are in any sequence.
How can i merge all the columns from Column D to Column Y in Column Z. The seperator between the Columncontents should be numbers in format "<<1>>" for column D, "<<2>>" for column E,"<<2>>" for column F........
i have a wordlist(arabic from right to left lanuage) containing words and meanings. Column A contains Words, Colulmn B contains Transcription, Column C contains Grammer information and the following Columns up to Column Y contain of Meanings.
!!!The meanings start with Column D which is then followed by E,F,G... Y. If any column cell is empty then it would mean, that the following cells of corresponding columns are also empty. For example if Colulmn D has content but Column E not, then it means that F,G,H...Y are also empty!
I am trying to find a function which will do the trial and error bit for me. I have for example 5 numbers an want to fit the answer to the puzzle.
For instance
3 5 X 2 3 Y 1 2 4
Is there a way to find "X"? Then as a result "Y". I can't just go back up the equation as it is not hat simple. Is there a way excel will try an find what fits?
I have 10 separate columns with an unlimited (but right now 354 rows) and I would like to search for two matching numbers going row by row, giving me the answer in one field that would say how many rows the two matching numbers are in, is there a way?
I have a very large file with two columns with about 40,000 rows, is there an excel formula to match the same numbers in column A to the same numbers in column B and all the other non matching numbers to be pushed to the bottom of the columns.
I am using Excel 2003 and trying to create a formula that will combine comma delimited numbers from two or three cells in the same row and output them to another cell in the same row as comma delimited numbers, sorted with duplicates removed.
So the data is something like this ...
Cell A1 ... 1,5,8,19
Cell B1 ... 4,26,1,8,8501
Cell C1 ... 1,6,1301,12
I would like to consolidate these into cell D1, sorted and distinct ...
I want a code (Formula) do the follwoing: Matching number 1 in column S with number 3 in Column O and count how many matches found in the two columns, Which in this case only two.
I have two tables. One has long list of numbers, and the other one has lesser. I'd like to match the identical numbers in both tables. By the way the first table has 14600 cells and the short one has 1466 cells.
i have a userform in excel that the user enters a form number into a textbox. As these forms come in a book of 25 i need the user to have the option of entering a 'Start" and "End" number into two textboxes - this i can handle.
What i'm having problems with is working out the code that would make the following happen
When they click the update button i need for the form numbers to match up with the original ones already in a sheet e.g in column "C" is the list of original form numbers. (These are in groups of 25). When we have the copies returned i need to be able to "match" these against the ones listed in column "C". the matched numbers would appear in column "F". most of the time the copies arrive back in "book" lots yet occasionally we may just get a few back at a time.
In Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
I have 4 Columns. Column A has a master list of inventory numbers(about 3000 of them). Column B has dollar figures associated with them. Column C has some inventory numbers in it(800 of them), all of which are also in the master list in Column A. Column D has dollar figures that are associated with the inventory numbers in Column C.
My question is how can I make it so that if an inventory number in Column C equals one in Column A that the dollar figure associated with the inventory number in Column C gets put into its own column on the same row as the inventory number it was equal to in Column A.
I've tried basic IF functions and I can't get it to work because the inventory numbers aren't lined up on the same row. So one may be on row 300 but the other maybe located in row 12. Maybe if a double sort was possible or if someone knows how to make the row in the IF statement a variable.
