How To Convert A List Into List Of Consecutive Numbers
May 15, 2013
Let me see if I can explain my question in an understandable fashion....
I have a table containing data for about 2000 ID numbers. Some of these numbers are unique and some are duplicates. I would like to convert the ID numbers into a consecutive list of integers while preserving the unique numbers. For example, if the first column of my table is currently:
ID#
18578
19644
19644
20247
20974
21361
21361
21419
I would like to change that to something like:
ID#
1
2
2
3
4
5
5
6
I need to know which records (i.e. which rows of data) are from the same ID# but want to remove the actual ID#.
View 6 Replies
ADVERTISEMENT
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 2 Replies
View Related
Feb 28, 2014
I am trying to create a column which lists numbers consecutively from 1 - 10,000, grouped in intervals of 20 (ex: 1 - 20, 21 - 40, 41 - 60, 61 - 80, ...) without having to enter them all manually. Is there anyway to do this in Excel?
View 3 Replies
View Related
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 5 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Feb 25, 2014
Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
View 8 Replies
View Related
Jun 5, 2014
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
View 2 Replies
View Related
Mar 21, 2014
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
View 3 Replies
View Related
Nov 29, 2007
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 9 Replies
View Related
May 23, 2007
What I need specifically is a situation where you choose the Month/Year in a drop down menu in say A1, and then cells B1-Bx display the dates of each day of the month. I’ve been reading tutorials at breakneck speed, and will probably continue to do so.
View 2 Replies
View Related
Jun 26, 2013
I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.
Column G
Compensation
Payroll
Column H
COMPENSATION
Savings Box
Update Information General PAYROLL
Payment Inquiry Update Information Severance Pay
As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.
View 2 Replies
View Related
Jun 14, 2013
I have 2 lists and need replace the numbers in List 1 if they appear in List 2. If they do not match to list 2 then keep the number in List 1. I have attached a excel worksheet as an example. So cell B4 needs to be replaced by Amount in Cell F9. Also Cell B7 needs to be replaced by number in F5.
View 3 Replies
View Related
Sep 26, 2008
I have a data array which i would like to convert to a list. Sort of the reverse of a Pivot Table
See example below
I would like to turn this
Account Jan Feb Mar April etc..
001 59 30 25 40
002 12 20 32 29
003 5 13 27 39
004 10 11 12 13
Into This
Account Month Amount
001 Jan 59
001 Feb 30
001 Mar 25
001 April 40
002 Jan 12
002 Feb 20
002 Mar 32
002 April 29
003 etc...
There is a sample of the data file attached.
View 3 Replies
View Related
Jul 6, 2009
I have a list of data i would like to convert to a different format using a macro.
The conversion involves Sort the data. Delete rows when criteria is met. Insert blank rows when criteria is met. Insert formulas into cells. I have attached a sample workbook.
View 2 Replies
View Related
Jun 14, 2012
I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?
View 2 Replies
View Related
Jun 3, 2014
I am trying to convert a table into a list, please see the example, I want to convert this table
10547
aaa
140x200
160x230
300x400
10549
bbb
140x200
150x260
[Code] ........
View 1 Replies
View Related
Sep 18, 2008
I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?
Table
X Y Z
A 3 5 7
B 2 4 6
C 8 9 10
List
Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10
View 9 Replies
View Related
Jun 24, 2007
I want to take information from a list and put into a matrix using VBA. So my problem is to find the correct cell in the matrix.
View 5 Replies
View Related
Jun 5, 2014
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies
View Related
Dec 18, 2007
I'm looking for a way to automatically convert a list of values into a matrix.
For example,
A1A11000
A1A2998
A1A3468
A1A4491
A2A1998
[Code] .......
should be converted to
A1A2A3A4
A11000998468491
A29981000464488
A34684641000992
A44914889921000
In this case it could still easily be done manually, but if the matrix has more dimensions it will get harder.
View 5 Replies
View Related
Nov 8, 2012
I have a wedding list in rows in an excel sheet IE Name, Address, City, State, Zip
I want to transfer it to a text deliminated file in notepad, where it will appear as follows:
Name
Address
City, State Zip
how to accomplish this.
View 1 Replies
View Related
Oct 9, 2007
I would like to convert the data by VBA macro from a table format into a list format. The convention of the TABLE data is as follows:
First row is Header Titles
[TABLE]
[SITE],[LOC],[PROD], [MTH1], [MTH2], to ->[MTH12]
North, Office, Cooling, 100, 125, 85
[/TABLE]
I would like the data to be provided into a list format for each month.
For example:
[TABLE]
Row 1: North, Office, Cooling, Month 1, Value
Row 2: North, Office, Heating, Month 1, Value
Row 3: North, Office, Electricity, Month 1, Value
Row 4: North, Retail, Cooling, Month 1, Value
Row 5: North, Retail, Heating, Month 1, Value
Row 6: North, Retail, Electricity, Month 1, Value
etc
[/TABLE]
View 7 Replies
View Related
Jul 10, 2012
I am trying to find a efficient solution to convert 3 lists (in the same column layouts) to a calender format. I have different lists that are maintained but want to consolidate them into one calender. So rather than looking at 3 individual lists, someone can view this one master calender with all the events/meetings.
The events from each list would automatically populate one master calender.
List 1: Abe
Event Date__Discription
1/20/2012__Lunch meeting
3/2/2012__Convention meeting
5/11/2012__Dinner meeting
List 2: Bill
Event Date__Discription
1/4/2012__Office Meeting
6/2/2012__Golf meeeting
[code]....
Is there an existing program or file out that can accomplish?
View 1 Replies
View Related
Apr 10, 2009
I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.
The data is currently in the format below (notice how one entry has a website while the other does not).
First Church
102 Main Street
Dallas, TX 12345
email@whatever.com
Second Church
500 Second Street
Austin, TX 12376
email2@whatever.com
http://www.boguswebsite.com
So I'm looking for the data to be formatted like the following:
First Church 102 Main Street Dallas, TX 12345 email@whatever.com
Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
I was able to find the following code from a Google search, but it can't dynamically adjust the range.
Sub x()
Dim rng As Range
Set rng = Range("A1").Resize(5)
Do Until IsEmpty(rng.Cells(1, 1))
rng.Copy
Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
Set rng = rng.Offset(5)
Loop
End Sub
I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.
View 9 Replies
View Related
Jul 25, 2009
I need to rename some jpg's in the c: emp directory ( about 600 ! )
In Col A starting at A1 I have the following:-
DFCF0156
DFCF0157
DFCF0159
DFCF0161
DFCF0164
etc
In Col B starting at B1 I have the following:-
ID3425
ID5823
ID427
ID7272
ID8875
etc
In c: emp is have:-
DFCF0156.JPG
DFCF0157.JPG
DFCF0159.JPG
DFCF0161.JPG
DFCF0164.JPG
etc
What I want to end with in C:TEMP is:-
ID3425.JPG
ID5823.JPG
ID427.JPG
ID7272.JPG
ID8875.JPG
View 9 Replies
View Related
Oct 27, 2006
I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.
What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.
Example:
The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad
but I need:
Dog,Cat,House,Car,Boat,Mom,Dad
If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.
View 4 Replies
View Related
Feb 20, 2007
i am working on a long drop-down list which consists of a series of numbers, such as 1,2,3.... When users select 2 from the drop down list, the cell value will be automatically converted to the value "movie" that "2" is corresponding to.
The list behind the drop-down list is like this:
column A column B
1 music
2 movies
3 books
4 games
5 travel
View 9 Replies
View Related
Aug 18, 2014
[URL] .... If you take a look at the picture, what I'm trying to do is add the list of Prices up so that I can differentiate the amount received in $ and in £.
I need to add up all the numbers that don't have a $ in front into the 1 cell, and all the ones with the $ in front into another cell.
FEdLkuj.jpg
View 2 Replies
View Related
Mar 15, 2014
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies
View Related
Aug 21, 2014
I have a list or log that is updated by a number of people on a sharepoint file. The list consists essentially of 2 columns - lets call them Location and Date.
The Locations are populated from a pulldown list but can be repeated a number of times throughout the list. I have the list set up using Table Formatting so the range updates dynamically
Example:
Location Date
Site1 8/1
Site2 8/2
Site3 8/3
Site4 8/5
Site2 8/6
Site2 8/7
Site3 8/8
As this list gets items added to it I want to populate a summary table on another sheet showing the dates each site was visited like this...
____8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 8/9
Site1 X
Site2 -----X-------------X---X
Site3 -------X
Site4 --------------X
(Had to add dashes above to get the X's spaced out properly)
I'm competent with lookups and such but I imagine this needs an array formula or some index/match combination which I'm a little weak on.
The solution should also not require any updating as the source list is updated periodically.
View 4 Replies
View Related
