How To Combine Set Of Two Numbers
Dec 13, 2011
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 ?
For Example :-
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAAAegAAAJuCAIAAAD0MwYDAAAgAElEQVR4nO2dvY4ct7ZG+SxOlE9qKJlAGBwoOIESC05H
iaHEUDA4AgxHhgbOL k4wgOMJ/QKK/AgODNwnuIleYm7QP8UiN1n/tfcuroUOLHb9sNi917DZ7frC3//7fzx48
[Code]....
View 3 Replies
ADVERTISEMENT
Aug 30, 2007
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?
View 9 Replies
View Related
Jan 29, 2009
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
=vlookup(1001,table,2,false)+vlookup(1002,table,2,false)+vlookup(1003,table,2,false)+vlookup(1004,ta ble,2,false)+vlookup(1005,table,2,false)
Is there possibility to define a function that works like say =clookup(numlow,numhigh,table,2,false)?
View 8 Replies
View Related
Jul 20, 2012
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.
View 3 Replies
View Related
May 27, 2009
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!
View 6 Replies
View Related
Sep 18, 2013
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
View 2 Replies
View Related
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
View 1 Replies
View Related
Dec 11, 2012
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 ...
Cell D1 ... 1,4,5,6,8,12,19,26,1301,8501
View 1 Replies
View Related
Apr 16, 2008
I basically need to grab all the numbers going down in a row and get them into one cell in Text format with a comma between them.
A1
5293
5294
5295
5296
5299
5300
5301
5302
5303
5304
5305
B1
What I need in Text format
5293,5294,5295……
View 3 Replies
View Related
Feb 10, 2010
I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.
So for example:
Worksheet 1 (January)
Column A Column H
Asset # Total Expense
5021 $3,041.52
970289 $242.08
Worksheet 13
Column A Column B Column C Column D
Asset # Asset Description January February
5021 Rivet Machine $3041.52 $345.65
So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.
View 7 Replies
View Related
Feb 27, 2014
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
View 7 Replies
View Related
Mar 5, 2012
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
View 4 Replies
View Related
Feb 20, 2013
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
View 3 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 Replies
View Related
Apr 16, 2013
I need to get 5 small subs into 1 sub. (run the whole program by pressing once.
Please see attached file with codes : ServiceDriftMaaling.rar
View 6 Replies
View Related
Mar 29, 2014
Ok, These are the three formula's. How do I combine them or can I.
=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)
=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)
=IF((B2="COM"),[@[Gal Billed]]/1000*3)
View 4 Replies
View Related
Jan 29, 2014
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies
View Related
Nov 9, 2007
I have two formulas that i need to combine as one, but i dont now how.
This is the other formula:
=SUMPRODUCT((Huollot!I2:I500="Ty”n alla")*(Huollot!K2:K500="Warranty")*1)
The other one:
=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
View 14 Replies
View Related
Nov 26, 2008
In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with
View 2 Replies
View Related
Dec 24, 2008
I want to combine number in one row.
Column A
12
13
14
15
Need to be in column B. 12,13,14,15. I tried “=A1&”,”&??
View 3 Replies
View Related
Jan 8, 2009
Trying to write an IF query looking for multiple returns that would equal the same result.
I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.
1 or 6 = 1
2 or 7 = 2
I have been trying unsuccessfully to combine the IF and OR queries
View 12 Replies
View Related
Jan 23, 2009
I am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.
View 9 Replies
View Related
Jun 24, 2009
I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
View 6 Replies
View Related
Oct 15, 2009
i m working on a spreadsheet and i need cell A+B=C..
Example
Cell A has a first name (John)
Cell B has a last name (Smith)
Cell C has last name then first name seperated with comma
View 4 Replies
View Related
Jan 23, 2013
Is it possible to combine three cells into one? Like for example, column A is the first name, column B is the middle name, column C is last name.
Is it possible put it on a format like this: Last, First M.
(middle name on column B is a name, not an initial.)
And can I make it only the first letters are capped. Last, First M. Or all small letters. last, first m.
View 7 Replies
View Related
Apr 1, 2013
I have:
- Table 1 has ~1,600 records (name of game, meta score, user score)
- Table 2 has ~3,000 records (name of game, genre, publisher)
I want:
- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)
Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.
I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).
Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that
View 1 Replies
View Related
May 1, 2013
combine two excel worksheet into one new worksheet. thing is on both of these worksheet doesnt have a unique identifier. Is that possible & how.
View 2 Replies
View Related
Aug 18, 2014
On Sheet1 in Range A1:A10, I have the following data: O O M3 M O O M M D9 O (in reality, the data covers the range K38:AN90, but I'm simplifying my requirement here)
On Sheet2, in Range A1:A10, I have the following data: O O O O A1V O O O O D1V
I need some VBA code that will loop through each cell on Sheet2 and where it comes to any containing the letter 'V', it will replace the data on corresponding cell on Sheet1 with that data.
In this example, after running the code, Sheet1 will read: O O M3 M A1V O M M D9 D1V.
The only other caveat here is that I need to be able to return to the original data on Sheet1 but I'm thinking I'll add some code to toggle back by making a back this up on a separate sheet so I can immediately reinstate original data on Sheet1.
I want to avoid 'Copy/Paste' and if at all possible so prefer to use Range("X").Value = Range("Y").Value etc.
View 2 Replies
View Related
Jan 23, 2009
I have a scenario here and i really dont even know where to start. If anyone can help me with some code for this project that would be great.
I have a folder that contains several subfolders (10-20). Within each subfolder is a number of excel files (the number of files varies) that contain 2 worksheets each.
What i am trying to arrive at is one file for each subfolder that has the first tab of each of the files. The original files contain links, so the values and formats must be pasted. It woul dbe great if all of the new files could be saved in the main folder among all of the subfolders.
It would be especially great if each tab could be named for its respective file and each new file for its respective folder.
View 6 Replies
View Related
Mar 14, 2009
I probably have a odd request, but then I think I'm just different ?
I have an Excel Workbook with Col. A approximately 1/8" wide. The formatting of this col is that the cells are set to have the typing fit into the cell, therefore it becomes so small that no one can read it. Just what I wanted. If the user needs to read what's in the cell, they can click on the cell and read it in the formula bar.
Col. B is approximately 2" wide
1st, the user enters the Path and File name, with extension, into cell A34(example).
2nd, the user types in a File Name that they will best understand into cell B34.
The Workbook is now saved.
When the user wants to open that file, they click on the Name . . . Col B34 - Easily recognized by the user.
They then click either the Button that say's "Open Excel File" -or- "Open Word File.
The macro automatically moves the selection over to column A, then the file is opened.
Now what I need
I want to use just 1 button and I will name it "OPEN"
The 2 below macros work excellant and I still need the functionality of each. What I want is that 1st it trys to see if it is an excel file and if so, opens the file and stops.
If the macro determines that it is not an Excel file, the macro then goes to the Word macro and determines if it really is a Word file and if so, opens the file and stops.
NOTE: I want to point out that I still need all the checking etc that the 2 macros currently do.
Here are the macros
OPEN EXCEL FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
View 10 Replies
View Related
