Sort With Comma Delimited Numbers

Jul 7, 2014

make a macro wich it's going to sort comma delimited number in the correct order.. see attached file to get the wanted reult..

View 5 Replies


ADVERTISEMENT

Splitting Comma Delimited Numbers Into New Columns

Apr 20, 2009

I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View 12 Replies View Related

Create Individual Comma Delimited Cells From Delimited Cell

May 30, 2008

I need to create a comma delimited list based on variable start and end values for each row.

StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008

I'm not a VBA expert, or I would have created a Do While or For Each loop.

View 4 Replies View Related

SUM Of Values In A Comma Delimited Cell

Jan 13, 2010

Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.

View 14 Replies View Related

Saving To The CSV (comma Delimited) File

Dec 1, 2006

I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW
9057926500

What is looks like after save

9.06E+09

If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View 9 Replies View Related

Text File But Comma Delimited

Sep 28, 2007

In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows.
I would like to export the first three columns only (Column A, B and C). The TXT file should have the following:
First line: "This file was exported from Excel"
Second line: The value of cell B4 of the worksheet
Third line: The value of cell B5 of the worksheet
Fourth line: Today's date ( as 27/Sep/2007)
Fifth line: No entries (Empty row)
Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value.
Seventh line: Cell A13 value, cell B13 value and cell c13 value.
etc, until the end or selected amount of rows.
The values of the different columns should be separated by commas
Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789)
Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)

The text file therefor would look as follows:

This file was exported from Excel.
Company ABC
On this continent
27/Sep/2007

123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876

I tried to adapt jindon's code but no luck.
I am currently doing it the long way.
='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12
This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.

View 9 Replies View Related

Search Comma Delimited String

Dec 1, 2009

I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?

View 9 Replies View Related

Data In Column A To Comma Delimited List

May 31, 2012

excel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...

food
drinks
home
shelter
ocean
water

to this ---> food, drinks, home, shelter, ocean, water

View 5 Replies View Related

Open All Txt Files (delimited Tab Comma ) In A Folder

Aug 11, 2009

I have around 30 files each quarter which I need to convert from a text file to an excel spreadsheet. I am a beginner with VBA and am looking for generic code I can use for a macro to:

1. open a text file from a folder
2. delimited/tab/comma
3. format columns H, O, and AH into dates
4. 'leave a spot for me to insert my code to manipulate the data'
5. save the file as an excel spreadsheet to a folder with the same filename
6. loop to perform this task to all files in a folder and stop after the last file.

The dilemna I am having is that my text files do not have a suffix ".txt" after them.

They just have the file name ***MMDDYYYY. There are always 3 initials at the beginning which change for each file i.e. ABC06302009.

The date remains the same for the given quarter, i.e. ***06302009. Next quarter I will have to do this same thing for all files ***09302009.

View 9 Replies View Related

Separate Comma-delimited Values To Rows

Oct 22, 2009

I have an excel sheet I create with lots of inventory items distributed amongst several locations. The first column is a location field where several 1-2 digit location codes are seperated by commas. I would love to get the macro to copy each row, once for each location code, onto a new tab as a new longer list. Because the amount of data per row varies, I need the entire row to be copied to the new tab....

View 10 Replies View Related

Import Comma Delimited Info In A Column

Mar 4, 2006

I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.

View 10 Replies View Related

Convert Comma Delimited Cell To An Array

Feb 15, 2010

{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.


I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.


{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.

View 9 Replies View Related

Convert A Column Into Comma Delimited List

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

Separate Comma Delimited Cells To New Rows

Mar 31, 2008

I have a 5K rows of data, each including a cell (AD) of multiple number values, separated by comma. I would like to create a new row for each unique number in column AD such that there remains only one unique value for every AD cell.

If possible, I would prefer an in-cell formula rather than a macro.

View 9 Replies View Related

Remove The Extra White Space Between Each Comma Delimited Digit

Oct 25, 2009

This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345

My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:

"A B C 12345"

So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?

View 3 Replies View Related

Transpose Column Into Unique Rows With Comma Delimited Values

Nov 4, 2013

I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

GA-AG-00010-A-2013
Apache Hunting Club

709

36

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

[Code] ......

How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

View 9 Replies View Related

Pivot Table Labels Based On Comma Delimited Cell Values

Jan 25, 2010

"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.

The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."

I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!

View 2 Replies View Related

Macro To Save Excel Sheet As Comma Delimited Text File?

Apr 21, 2014

providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.

View 14 Replies View Related

Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

Mar 30, 2013

Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.

I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...

Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.

Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .

Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).

Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).

E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"

Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....

Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F

View 7 Replies View Related

Dropdown List From Comma Delimited List In Single Cell

May 9, 2014

Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.

View 3 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

View 5 Replies View Related

Reversing Alphabet Sort & Remove Comma

Dec 1, 2009

Is it possible to take a list that is alphabetized in the following manner--Jones, Mary--, convert it to Mary Jones while also removing the comma after the name?

The =TRIM and =MID formulas that I saw in another thread both seem to convert the text, but they also leave the comma at the end.

View 2 Replies View Related

Excel 2003 :: Formula To Combine Delimited Numbers

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

Concatenate Numbers With Comma If 2 Other Conditions Are Met?

Mar 31, 2014

I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.

Let's say, my numbers are in Row 1, from B1 to N1.

My conditional values are in Rows 5 and 6 respectively, also from B to N.

Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.

I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.

Row 1: 1 2 3
Row 5: Name Name Name
Row 6: Date in Feb, Date in March, Date in March

Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:

Name+March -> 2, 3
Name+February -> 1

View 2 Replies View Related

How To Format Numbers To Put Comma After Every 4 Digits

Oct 15, 2013

I need to list 4 digit numbers in each section followed by commas, but whatever I do it goes to 3digits (e.g. I need "1234,5678,9123" and as soon as I hit Enter it goes to "123,456,789,123"). It wont work to format as text because I have a whole bunch of 12 digit numbers to break up into 4.

View 9 Replies View Related

Insert Comma Between Letters And Numbers

Jan 30, 2014

I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.

ABCD90bj10r7
ABCD,90,bj,10,r,7

ABCD90rt8r7
ABCD,90,rt,8,r,7

iFH15jr7ri12
iFH,15,jr,7,ri,12

iFG155jr8ri11
iFG,155,jr,8,ri,11

iFG15jr8ri9
iFG,15,jr,8,ri,9

View 2 Replies View Related

Comma In Each Cell To Separate Numbers

Apr 18, 2008

I want to put in each cell a Punctation mark (comma), like this: ,
... when I have in each cell a number

I don't know how;

View 4 Replies View Related

Separate Numbers From Characters That Are Separated By Comma

Feb 17, 2010

For example the cell contains 1,M2,M7,M1,M8:2,M15,M9,M4,M5:3,M3,M6,M14,M11,M12:4,M10,M13 and I need to create M1, M2 , M3 ..etc columns and attach the coresponding number to each one of them. For instance M2,M7,M1,M8will get 1 and so forth and so on. I was acutally thinking using the Notepad to replace the , with a TAB space and paste them back in to my data set and create some IF statements. On the other hand running VBA scrip would make it even easier. However I would preffer to use a function (that I am not 100% familiar with) and leave the VBA scrip out of the question for the moment.

View 12 Replies View Related

Find Number In A Cell With More Than 2 Numbers Separated By Comma

Oct 7, 2006

When the entry in a cell is of the format e.g 3,4 is there any way to find the value 3 or 4 in that cell?

Attempts like:

Cells(i, 1).Value = variable (e.g variable=3)

or

Set cell_found = Cells.Range("A1:A10").Find(variable, lookat:=xlWhole)

do not find the cell with the 3 (or 4). The above work fine when 3 or 4 are alone in the cell. Is there anyway to find these values in the cell?

View 4 Replies View Related

Combine Numbers Into Single Cell, Comma Separated

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







Copyrights 2005-15 www.BigResource.com, All rights reserved