Combine Numbers In A Row From A List Of Numbers
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
List Only Whole Numbers From Decimal Numbers
From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed For example 34.95 21.88 21.00 56.00 45.77 Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents
View Replies!
View Related
How To Combine Columns D-Y In Column Z Seperated By Numbers
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 Replies!
View Related
Vlookup A Range Of Numbers And Combine The Sum
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 Replies!
View Related
Manage A List- List Full With Numbers In A Column
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number. Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits) PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error. 2- I have another list with 2 columns which arranged in order. I mean: A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column. My request is I want to make this list 1 column as arranged. A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc. 3- Macro will add a new line to the end of the above list with this format: Z00001xxxx Z00001 is static xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits. Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
View Replies!
View Related
Convert Downloaded Web Page Numbers Seen As Text To Numbers
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?
View Replies!
View Related
Compare 2 Columns For Numbers In Mixed Text & Numbers
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
View Replies!
View Related
Average Of The First N Numbers In A List
Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list. I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.
View Replies!
View Related
Cross List Of Numbers
If i have two list of numbers one list in cells A1:a50 and one B1:b50 i want in c1:c50 to see me the same numbers Also if i have more lists of numbers a1:a50 b1:b50 c1:c50 d1:d50 e1:e50 then in row F show me the numbers that they are in all lists
View Replies!
View Related
Add Numbers In A List
I need to add all the values in column B for each specific day in Column A and display them in Column C. For example the sum of all values in Column B for Monday is 183 which is then displayed in Column C.....
View Replies!
View Related
Finding Common (repeated) Numbers In Columns Of Numbers
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months. If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
View Replies!
View Related
Turning Red Font Numbers To Negative Numbers.
Usually this question is asked the other way around, but I have a somewhat unique problem. A certain website gives out tables filled with numbers. Positive numbers show in black font and negative numbers show in red font, but unfortunately, negative numbers do not include the minus sign -- the font is red and that's it! I need a macro (or any other solution) that will turn the red font numbers to negative ones and would possibly format the cell to show negative numbers in red (I guess the last part is easier). The main problem is searching for the red font numbers and turning them negative.
View Replies!
View Related
Extracting Numbers From Alphanumeric Strings (strip Numbers?)
I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "-1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective. Some ideas I had that I don't know how to implement: is there a way to strip non-numerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc. Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value). I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.
View Replies!
View Related
Delete Numeric Series Numbers Between Numbers Entered
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example. Series 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 and i want to delete 1 to 5 numbers delete to shift cells up.
View Replies!
View Related
Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers: Sub ForceToNumber() Dim wSheet As Worksheet For Each wSheet In Worksheets With wSheet . Range("IV65536") = vbNullString .Range("IV65536").Copy .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End With Next wSheet End Sub Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.
View Replies!
View Related
List/Fill Numbers Between Two Values
I have two numbers...let's say 1 to 10. I would like to fill in the middle numbers to lst in Excel as 1 2 3 4 5 6 7 8 9 10. see attached for an example of waht I'm trying to do. I can manually do this with the fill handle but I have numbers to fill in the thousands in some cases and tens in others. I would like to automate this process if possible.
View Replies!
View Related
Creating List Of Remaining Numbers
I have a list of numbers . Several numbers are pulled, based on criteria, and then I need to create a list with just the remaining numbers. EXAMPLE: A) Numbers 1 - 500, defined by name (So I can INDEX them later) B) Pull out numbers 47, 3, 143, 224 (based on certain criteria) C) By INDEXING the field, create a list of numbers 1 - 500, omitting the above numbers. I have no problems with steps A & B. I can't do step C.
View Replies!
View Related
List Of People In Column A And In B To E There Are 4 Numbers From 1 To 13
I have a list of people in column A and in B to E there are 4 numbers from 1 to 13. (Each person has 4 different numbers). In the next 24 columns are concatenations of these numbers in every order possible. On a separate sheet I have a list of concatenations of all possible combinations of numers 1 to 13 in groups of 4. e.g. 1234, 1235 etc. I have then counted how many times each of the concatnations from the first sheet appear. What I want to do is put the names of the people whoes concatentaions match the ones on the third sheet.
View Replies!
View Related
Loop-code To Go Through A List Of Numbers
Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
View Replies!
View Related
Find Sum In List Of Of Numbers
I have a list of numbers in a column and I need to find which numbers when summed together equal a figure. I have a list of invoice amounts that I need to match up with payments (the payments are always made for several invoices so I need to come up with sums of several invoices to get to this payment amount). An example would be I have this in the following section (A1:A10): $17,213.82 $4,563.02 $85,693.42 $1,166.01 $725.90 $580.09 $2,243.75 $240.16 $207.70 $725.90 I need to find which combination of these figures would sum $1,173.76.
View Replies!
View Related
Extract The Non-negative Numbers From A List
i need a self correcting formula to solve the following case: In column A in 7 rows: 5 -9 3 2 -4 -7 1 I want to extract the positive numbers to column B in the amount of positive number rows(no skipped row/space): 5 3 2 1 So it would look somthing like this: A B 5 5 -9 3 3 2 2 1 -4 -7 1 Is there a simple formula to do this? I have been doing IF functions but it is taking too long. And I have got the results I wanted. =IF(A1>0,A1,"") ==> works but I want the numbers to be one right after the other (no blank rows in between).
View Replies!
View Related
Check Column Against List Of Numbers
What I would like to do is have a macro check a column for any occurrence of a few different numbers, and then enter the text "n/a" in a different column of each row that one of the numbers doesn't appear in. I have a list of part numbers that require a certain operation, and I'd like to mark off a cell for each item that does not require that operation. I've attached a simple spreadsheet that should help explain this better.
View Replies!
View Related
List Column Numbers Greater Than X
I have been browsing this site for some time but never posted. It looks like a great community and looking forward to learning! I am trying to create an array in VB that searches an entire column (L) that has nothing but numbers in it. I want to search for all numbers that are >= 6. If the number is greater than six store the value in column A. At the end, I need to display a message box that has all the values in A that match the criteria where column L is greater than 6. I have found all kinds of copy to other sheet formulas and static finds.
View Replies!
View Related
Find Values Not In A List Of Numbers
I have a table with numerous business units in column F. The business units are shown as a number. i.e. Business unit # 00007, 00041, etc. There are around 500 different business unit numbers. I have a list of 60 specific business units that I want separated, leaving a remainder of 440 others. I want these 440 other business units to stand out, ideally with conditional formatting.
View Replies!
View Related
Average Row Of Numbers Using Different Row #'s
Field 1 input: Beginning Date (e.g 7/17/09) Field 2 input: End Date (e.g. 7/23/09) Need: Average costs on and between these given dates From table with this information: Date Market two 7/16/2009 80 7/17/2009 80 7/20/2009 82 7/21/2009 84 7/22/2009 82 7/23/2009 82 7/24/2009 82 7/27/2009 82 I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it. If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82) How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
View Replies!
View Related
Picking 5 Highest/lowest Numbers From A List
I am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".
View Replies!
View Related
Generate Random Numbers From A Frequency List
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
View Replies!
View Related
Given A List Of Numbers (ID) Pair Up (roundrobin) Without Duplicating
This is my Excel dilemma: creating roundrobin pairs. For a given list of numbers, I need to generate pairs where each number is paired with all the other numbers without creating a duplicate pair. For example: 1,3,4,6... (dynamic) Pairs: 1-3, 1-4, 1-6, 3-4, 3-6, 4-6 Results should be generated in two columns. Column A would be ID1, Column B would be ID2 (the combination of both would be the pair created) ID1ID2131416343646 I have basic ideas for a macro but the loop within a loop and the OFFSET for the next row is something I really can't put together.
View Replies!
View Related
Cross Check A List Of Alphanumeric Numbers?
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid. The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
View Replies!
View Related
Search For A List Of Numbers And Alter Findings
Is there a function or macro that can take a list of about 200 numbers and search for these numbers throughout the workbook, If 1 of these numbers exists anywhere in the workbook, it changes the color of the number or does something to the number where I will know this number is part of the exception list? The list of 200 numbers in non changing, however the numbers I want searched will change daily. There will also be worksheets added and taken away that contains these numbers.
View Replies!
View Related
Sorting A List Of Numbers By Multiple Columns
I want to sort the list like this: 1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file). 2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change. Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.
View Replies!
View Related
List Missing Numbers From Numeric Series
I have a long list of numbers (1 to around 3000) in Column A and I need to print out only the numbers missing from that list. The numbers are sequential (except the ones missing) and there are no blank or missing cells, only missing numbers. EXAMPLE: 1 2 4 5 7 ..... So I would only need to print the number 3 and 6. - it can be all in one cell, or in one column, it doesnt matter.
View Replies!
View Related
Extracting Numbers :: Pull Numbers From Another Column
I'm trying to pull some numbers from another column. I want to pull the numbers that have an X separating them like 7X125, 48X192, and 27X90. Example: FA, VF-2000-3-7X125-18-A, AFS FA, VF-2350-48X192-6-RGB, FC FA, VF-2020-27X90-18-A,RFI, FEX, ACP, 2IT
View Replies!
View Related
Convert Numbers As Text To True Numbers
I have the following two macros that I use daily: Public Sub add_tick_marks() Dim c As Range For Each c In Selection If Not IsError(c.Value) Then If Left(c.Value, 1) <> "'" And c.Value <> "" Then If InStr(c.Value, "E+") > 0 Then c.Value = "'" & c.Formula Else I use these because I frequently copy billing account numbers that have leading zeros from some proprietary software and work with them in Excel. I need the leading zeros to facilitate VLookUp's. When I paste the data into Excel it truncates the leading zeros. My typical procedure is: 1. Copy a column of values from our proprietary software 2. Paste it in Excel (Ctrl+V) 3. While newly pasted data is still selected I hit Ctrl+1 which brings up the Format Cells form. 4. I double click "Text" 5. I re-paste into Excel (Ctrl+V) 6. I run my macro to add tic marks to the values so the leading zeros never get lost. The problem occurs when I copy multiple columns of data and paste it in Excel. If one of the columns of data contains numbers or currency then I select that range of values and run the macro to get rid of the tic marks. After running the macro the numbers are still treated as text. When I select a range of those values and right-click on the status bar and select " Sum" there is no value. No big deal, I just highlight them and format the cells as numbers or currency. But after formatting them as numbers or currency, they retain their format as text until I click in the formula bar for each individual cell and hit enter, at which time they take on the number or currency format I have chosen and the sum can then be displayed in the status bar. How can I force the formatting I chose to take effect on the data immediately without me having to individually "fake change" the data in each cell?
View Replies!
View Related
Convert Numbers Seen As Text To True Numbers
I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file
View Replies!
View Related
|
TRACKER
