Excel Magic Trick #133: Import CSV Data (Comma Separated Values - Data)

Hi,

I have a range of data which I'm trying to save into a comma separated .txt file. Excel only allows one to save to a comma separated .csv file. Unfortunately the program I am trying to upload to requires comma separated .txt file.

I tried save to .csv file then opening in wordpad and saving as .txt file but unfortunately this means some of the blank fields are lost.

Please see attached file where the range of data I wish to export to comma separated .csv file is cells B1 : CI36. Further column T (Date) needs to be saved to .txt file in text format ddmmyyyy (i.e.: no "/").

When opening the .txt file the first line of the attached file would read:
,PYMNT12MAY11,,01,,RIV001,,,,,,,56001811149035,,,GBP,8640,,12052011,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Is the best way to write a macro to export the data?

Kind regards,
Sky

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Hello,

I enter bunch of serial numbers into one cell. 123, 224, 139, 124
There is a table with corresponding numbers. Like 123 = AKM
124=KLM 139=CSA & 244=LMN (please do not try to solve this. They are random letters for example )
the answer should come back as AKM, LMN, CSA, KLM in one cell.
A B
---- -----
123 AKM
124 KLM
.
.
139 CSA
.
.
244 LMN

Lets say they are from A1 to B300
The Serial numbers are entered in D1 and I like to get answers in E1.
example D1 [ 123, 139 ] answer in E1 [AKM, CSA]

I would like to do this with equation instead of VBA if possible.

Thank you,
Harry

http://www.youtube.com/watch?v=tqCEY5YMyqw

I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

Mine is as follows:
{=IF(ROWS(A$5:A5)>$B$2,"",
INDEX(US!A$5:A$72,
SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

Thank a million!
Long

Looking for a little help tweaking Excel Magic Trick 185. I've reached what is probably just a mental block.

I'm trying to adjust a spreadsheet for work using the processes described in Excel MagicTrick 185. (Great series, by the way...) The catch is that the source data table can not be modified under any circumstances (we've already asked).

The problem is different than EMT_185 in that there are 9 columns that can potentially define a match as opposed to just the one shown in EMT_185. There are two user options ('I-III' and 'A-C') which in combination establish the headings for the 9 possible conditions (I-A, II-A, III-A ...) .

(See the linked spreadsheet) I've been able to:
determine if a row in the source matches user provided conditions (col Y),
get the count of matched source rows (row 26),
determine the match instance (col AA), and
place the source data in the output table (col AC)

What I can't quite get my mind around is suppressing the blanks in the output (i.e.:getting match 1 on row 1, match 2 on row 2, etc.)

What am I missing? Please help. A spreadsheet showing what I have so far is located on google docs. Perhaps you could make a new EMT out of it!

BTW.. this used to be done via vba, however that is no longer an option (don't ask... It just isn't, and it's not a topic that is open to discussion -- we tried).

Ladies, Gents,

I need to turn a table of numbers (originally from a txt file) into a continuous list of numbers (in number format) separted by a comma and space. The final application is expecting numbers, not just text that looks like a number.

ie:- 1 2 3 4 (initially cut and pasted from text file)
5 6 7 8

turns into:- 1, 2, 3, 4, 5, 6, 7, 8

I have a macro that concatenates the data into the list (as per example above). I have been copying and pasting to notepad and saving as txt file.

1/ If it was a numerical list initially (in excel), it probably not anymore after it's saved as a txt file?
2/ The final application is on a stand alone computer and only has notepad - no excel. Excel may need to be installed simply to be able to transfer the data directly from excel rather than using notepad as a middle man.

Can a number list (with number as actual numbers and not a text look alike) separated by comma's and spaces be made and cut and pasted into the application via excel? ie the application computer will need excel installed if a proper nuber list can be made in excel.

OR

Is it possible to use notepad as the exchange medium?

I would type it in, trouble is I wouldn't live long enough!!!

Any advice is appreciated.

Regards

Chris

In an attempt to make my job easier, I am having to review data from one web site against data from another web site. This also means translating data from the data on Website A to match the appropriate data on Website B. For example (just pretend UPCs are that short).

Code:

Worksheet A
UPC  SiteAID  Name    Associated
001  954      Fun     137,321
002  137      Happy   954,321
003  862      Sad     
004  432      Mad     554
005  684      Bored   874
006  321      Silly   954,137
008  554      Angry   432
010  874      Mellow  

Worksheet B
UPC  SiteBID  Name
001  ABD      Fun
002  TYF      Happy
006  MSD      Silly
007  IUE      Weird   
009  WQT      OK      

Worksheet C
UPC  AllID    Name    Associated
001  ABD      Fun     TYF,MSD
002  TYF      Happy   ABD,MSD
006  MSD      Silly   ABD,TYF
007  IUE      Weird
009  WQT      OK

As you can see, Worksheet A shows the associations with its own SiteID. Worksheet B doesn't show any associations, but would like to use the associations in Worksheet B with its own SiteIDs (as shown in Worksheet C, the final product).

A regular VLOOKUP won't work here, so after some searching, I did find some code that would do a MultiVLOOKUP, essentially putting the data from the cell into an array, performing a VLOOKUP on each value in that cell, and output its values separated by commas.

Code:

Option Explicit
Function MultiVLOOKUP(LookUpVal, LookUpRng As Range, LookUpCol As Long)
    Dim v, w, i, rng As Range

    v = Split(LookUpVal, ",")
    ReDim w(UBound(v, 1))

    For i = LBound(v, 1) To UBound(v, 1)
        w(i) = WorksheetFunction.VLookup(Val(v(i)), LookUpRng, LookUpCol, False)
    Next i

    MultiVLOOKUP = Join(w, ",")
End Function

From what I understand, I had to put these data as a new module in the Workbook (which I did), but every time I attempt to run the code, I get a #NAME? or #VALUE! error (depending on if I'm doing a VLOOKUP for the needed LookUpVal or using straight data for the LookUpVal).

From what I can tell, it's hitting some issues when it doesn't find matching data, but that's just my guess. Can anyone offer any insight into this issue?