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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Jul 9, 2012
want to match column A to column C and if they match take whats in column D and put it in column B.
Column C has multiple instances of Column A and I would like each instance's, which is the following column, value put into column b
View 2 Replies
View Related
Feb 22, 2007
I attached a txt file of a report we get out of our accounting system. It is not tab deliminated, when I open it in excel, each whole row is contained in 1 cell instead of being broke up into columns. Im not sure how to convert it, im sure it possible using vb or something?
View 3 Replies
View Related
Jul 15, 2009
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:
A|123|
B|456|
currently there is no PIPE following the last character (3 or 6) and i am getting this:
A|123
B|456
I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record.
Here's the code:
[Code] ......
View 10 Replies
View Related
Jul 15, 2009
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:
A|123|
B|456|
currently there is no PIPE following the last character (3 or 6) and i am getting this:
A|123
B|456
I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record. Here's the ...
View 4 Replies
View Related
Jun 9, 2009
I have a software package that requires serial number effectively data to be entered in a particular format.
As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.
The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,
The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.
I would like the data to end up in two separate columns as shown in example 2.
Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns
2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746
Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276...............................
View 12 Replies
View Related
Mar 12, 2013
I have tried to alter some existing code from this site to accommodate my needs with no luck.
I would like a macro to convert the following.
Column A1-C1 contains Headers Column A-C contains Simple data to be copied down as many rows as needed to match the number of delimited pieces in the following columns. Columns D-I contain Data delimited by 3 spaces. The number of delimited pieces of data can be different in each row. The number of Delimited pieces of data will be the same in each column of a particular row. Example: if D4 has 4 pieces of data separated by 3 space, E4 will also have 4 pieces of data separated by 3 space, F4 will also have 4 pieces of data separated by 3 space and so on to I4.
D5 may contain 5 pieces of data separated by 3 spaces through I5 all containing 5 pieces of data.
Column G may contain a single space within the data delimited by 3 spaces.
Columns D-I may only have 1 piece of data with no delimiter.
I would like the Delimited data to be split apart and a new row created for each piece of data. and copy the corresponding data in column A-C to each new row.
If this can be done in the same worksheet or copied to an existing worksheet that would be great.
Here is a link to a sample on Google Drive [URL]
I am using Excel 2010 but would like it to be compatible with 2003 as well.
As I mentioned I found some VBA to do what I need but it only does 4 columns and I cant figure out how to make it do the 9 I need.
View 7 Replies
View Related
Apr 24, 2008
I have two columns of data, one for Position, and one for description.
The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position
So I need to convert:
Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805
to:
Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805
Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows
View 9 Replies
View Related
Jun 4, 2014
Any easy way to convert Smith John to Smith,John? No need to lose the space.
View 5 Replies
View Related
Feb 23, 2014
I've a question. For instance
apple,lemon,strawberry
fruit
green,blue,white,black,grey
color
...
...
...
...
I've a list like above. How can we use lookup formulas from comma-separated cells?
white> color
lemon> fruit
View 8 Replies
View Related
Jun 21, 2014
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies
View Related
