0

This question is corresponding to this link VBA Excel : Extract data in specific format from CSV files

but i have a different problem here.

In CVS file, In the col C full of information about application softwares like Windows XP, Adobe, Office, .net Framework, vlc, etc.... So, in the col C i have around >1800 lines.

I want to look for Windows XP,Adobe, IBM, VLC etc in each & every col cell or in the entire col if there a match then paste the match value in the next column cell.

For example, if "Adobe" found then paste "Adobe" to the next col cell.

How can i perform using ìf or Lookup or is there any best way??

if(FIND("Adobe",C:C),"TRUE","FALSE")

=Lookup(Windows XP, C:C) would return "Windows XP" =Lookup(Adobe, C:C) would return "Adobe" =Lookup(IBM, C:C) would return "IBM "

I have uploaded the CVS file here!!

http://uploadmb.com/freeuploadservice.php?uploadmbID=1324042163&srv=www&filename=5200.csv

Community
  • 1
  • 1
linguini
  • 1,939
  • 5
  • 49
  • 79
  • why don't you just make a pivot table on your list ? – iDevlop Dec 16 '11 at 15:14
  • @iDevlop: As you see in the file col C has a list(i guess, it has that when i click on filter i can see the list). How can i perform, Lookup text if match found, paste it into the next cell. – linguini Dec 16 '11 at 15:21

2 Answers2

1

You could use a formula in column D to check for "Windows XP" or whatever data you're trying to find:

=IF(ISERROR(FIND("Windows XP",C2)),"","Windows XP")

Jesse

Jesse
  • 1,937
  • 3
  • 19
  • 28
  • Could you explain me a bit more, I am a newbie to VBA. Please give an example. Thank u. – linguini Dec 16 '11 at 18:43
  • Taking it apart, FIND("Windows XP",C2) checks the contents of cell C2 for the text "Windows XP" and returns the starting position of the results it finds. If it does not find it it returns an error (#VALUE!). ISERROR returns true if it is an error. IF checks for TRUE and returns the first argument else returns the second. – Jesse Dec 16 '11 at 22:37
  • Putting it all together, it checks for the text, returns TRUE if the text is NOT found, processes the IF to return either the text or nothing. – Jesse Dec 16 '11 at 22:41
0
Sub AddFormula()
    Dim lLR As Long
    lLR = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2").FormulaArray = "=INDEX($E$2:$E$15,MATCH(1,--ISNUMBER(SEARCH($E$2:$E$15,$C2,1)),0),1)"
    Range("D2").AutoFill Destination:=Range("D2:D" & lLR)

This formula code works fine.

linguini
  • 1,939
  • 5
  • 49
  • 79