Data > Import External Data > Import Data
and specified that its a delimited text and the delimiter is a comma ",". One of the columns in the row has got a pretty long text and contains certain new line characters as well. After extracting the CSV the data in this particular column is treated as a different data entries.
Somebody please help. I have added the file in xls format since csv upload is not permitted.
I am trying to pull information from one spreadsheet into another. The unique identifier is used several times and I need to pull ALL of that data over. I using a vlookup currently but it is taking only the first line of the unique identifier and not the other lines that are on the original spreadsheet. Again, I need to pull all of the data from the original spreadsheet to the new spreadsheet. What would the formula for that be?
View 2 Replies View RelatedI 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.
I need to filter a large database using multiple OR criterias.
Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.
I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.
Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE
I'm trying to sum a certain column. The column is dynamic and depends upon the threshold. So this is what I have
Sheet1
Div | 100+ |130+ |140+ ( might tell me (140+)
I have some weird problems that I don't know how to fix (or even explain). Mostly, my code is good and works well in all my spreadsheets but I find myself trying to debug stuff that should be working, but isn't working 100% as it should.
For example, I have a cell that counts information using
=NB($A$8:$A$1000)
BTW: NB is COUNT in french, just so you know...
The weird problem is, it seems to skip the first 10-15-20 rows; it doesn't count them. So the total in the cell should be around 150, but it's 129?...
Can someone give me an alternative code to count cells?
I have a client with a list of books, and they are trying to sort a column in a sheet that has LC classification codes. When just doing a normal A-Z sort, it looks like this:
RJ 41 .D53 F54 2005
RJ 416 .L4 C52 1999
RJ 420 .D5T97 2003
RJ 421 .H385 2006
RJ 426 .H43 P43 2005
RJ 431 .M37 2003
RJ 436 .A8 R33 2006
RJ 436 .A8 T498 2001
RJ 436 .A8 T87 2003
RJ 45 .N4 2000
RJ 45 .N418 2006
RJ 45 .R92 1998
RJ 47 .E39 2001
RJ 48 .H37 2000
RJ 48 .H37 2002
RJ 48.2 .C537 2004
RJ 48.2 .P347 2005
RJ 48.2 .P385 2005
RJ 48.2 .R93 2003
RJ 480 .P34 2005
RJ 482 .R48 L448 2004
RJ 486 .C45 2000
What is weird about it is that I would think that the numbers after the RJ would be in numeric order but it seems to be treating them as codes. So, instead of RJ 41, then RJ 45, it goes on with a series of things that start with RJ 41. Is it normal for this to happen, or is there a way to change it so that it goes 41, 45, 47, 48, then 416, 420, etc.
In the following code snippet;
strFill = "Query for" & strCommName
strFill = strFill & vbCrLf & strPitHrs
strFill = strFill & vbCrLf & "This query has a rating of"
The cell output is formatted this way;
"Query for SP500
9:30-4:15 EST
This query has a rating of"
And thats how the text shows if you copy and paste it BUT in the cell there is a square box at the end of each of the first two lines. Is the vbCrLf causing that? If so what should I be using instead so the text in the cell shows exactly like the bolded text above
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.
View 2 Replies View RelatedA client of ours has one particular file that has some very strange behavior as described below. This only occurs with the one particular file and the behavior is consistent across different PC's in both their office and ours. All other Excel files in both offices work fine. Here's what happens:
- you can use Windows Explorer to copy the file between drives without
a problem (as expected)
- if you open the file in Excel on one drive then save it back to that
same drive with either the same file name or a different file name, the
file works fine (as expected)
- if you open the file in Excel on one drive then do a "Save As" to
save it to a *different* drive with either the same file name or a
different file name (in default .xls format), the new file causes
errors in Excel every time you open it as per below (weird!)
- even if you do a "Save As" and choose a different format eg Excel
97-2003 or Excel 95, the error behavior is consistent (weird!).............
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View Relatedi have a tab delimited file that desperately needs cleaning up.
i would like this:
word1 def1
word1 def2
word1 def3
word2 def1a
word2 def1b
to look like this
word1 def1, def2, def3.
word2 def1, def2.
my problem is this dictionary goes on and on, and i would just like a simple way to find these duplicate entries and consolidate them into the form i showed above
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 RelatedI have a worksheet that has around 2000 rows each containing data as shown in the attached sample sheet. I want to replace all the squares (is this some form of delimiting or seperating character inserted as the data was imported?) with a space and leave the other data intact.
I've tried find & replace to do this without success, although editing each individual cell works. Please let me know as I need a way to do this for all cells at once as it would take an age to do each cell indivudually.
I have a column of data consisting of Name and Number, for example:
ABC 123
ABC DEF 789
AB D EFG 567
Is there any solution that can parse the number from the Name, which returns:
ABC 123
ABC DEF 789
AB D EFG 567
Figured out how to export to Pipe Delimited through coding but now I am faced with 2 complications.
1) The column order in the EXCEL file is not the Order I want it to Export in. (i.e. COB Date maybe in Column 'I' in the Excel File but when I export it into .txt I need it to be in the First Column)
2) I need to have the header name Changed in the .txt file (i.e. COB Date is the name in the EXCEL file but I need it to show as cob_date in the .txt file)
how to write content of an array and a tab to a file. I have all the file handling working, my questions is how to do the actual write. The items in the array are all text strings but this doesnt work
Write #1 myRng(i,j) & vbTab
with the obvious assumptions. I just want to write the array value and a tab to my tab-delimited file in the next location. tia.
I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process. The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.
One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet. Here is the relevant code from within the Web Browser's class module:
If URL Like "https://www.fedex.com/FedExMMA/downloadcenter.do*" Then
Application.Wait Now + TimeValue("00:00:01")
SendKeys "^a^c", False
Application.Wait Now + TimeValue("00:00:01")
ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3"). Range("A1")
End If
The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data. But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications. So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?
I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up??
View 2 Replies View RelatedI have multiple items (Country Names) in cells exported from another database with what appear to be delimiters (semicolons) that cannot be counted. I want to count the number of items (Countries) separated by the delimiters. Is there a simple way to do this?
Column 1 fields:
TAIWAN, PROVINCE OF CHINA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;
CANADA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;CANADA;
INDIA;BRAZIL;DENMARK;GREECE;POLAND;MOLDOVA, REPUBLIC OF;HUNGARY;UNITED STATES;UNITED KINGDOM;
Result needed in Column 2:
2
6
1
7
9
What is the formula needed to get the Column 2 result?
On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:
"This file is not in a recognizable format" - I click OK
"Delimited or Fixed Width (default)" - I change to Delimited
"Delimiters listed with Tab as default" - I change to Space
I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.
Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.
I am using Excel 2000.
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 Relatedmake 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 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] ......
I'm trying to determine a way to populate a listbox with the contents of a few cells full of vblf-delimited information.
It's almost working properly, but placing the first entry in the target cells last in the listbox and have no idea why it's behaving this way.
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 ...
Lets say I have in column A a series of cells populated with words or phrases delimited by ;; and I want them sorted within the cell. I.e.
This:
Word ;; Another word ;; This is a phrase
Should become
Another word ;; This is a phrase ;; Word
I am trying to open up an asterisk delimited file then shrink the data, then after I want to save it back as an asterisk delimited text file. How would this be done in vba, as well as manually?
View 2 Replies View RelatedI've a word form that collects free text and tick boxes. I'm reading these into separate rows on an excel sheets. So far what works...
I've got excel vba converting the word to a delimited text file
I've got excel vba to remove the erroneous rtns that people have entered into the free text that was things up.
In the free text there are commas entered as well as the tick boxes and this is a csv. Example
"how do I do this, I don't know",1,1,0,1,"really seems to be a challenge!"
If I just use excel to open the text file then the columns work out ok and on a single row - I think the 'text qualifier' is playing a role here.
But I can't replicate this in VBA. If I record a macro it's a query table and I don't know how to amend the code to read into rows.
