I've got a funky situation here that I think a script could solve, but I'm no good at scripting Excel, so there!
It's Excel 2007 btw.
My online store exports a CSV of orders along with the line items of the order. To accomplish this, it outputs one row as the customer, having name, order #, etc etc. Then it spits out consecutive rows where those fields are left blank, but the item fields are filled in. Looks something like this for example:
It actually looks alright in Excel, as the list of products sits nicely under the customer "row".
My problem is this, I've got thousands of rows, I'm looking for all the customers that have bought widget2 to get their name and Email so we can update them on the product.
I can't simply search for "widget2" and grab the row above, cause the row above might be another line item or whatever. So somehow I have to search for the widget2, then offset left and see if it's blank, if it is blank, then offset UP until it finds text (which would be the Email or name field). Once it finds the text, I need THAT row copied out onto another sheet to build the Email list with.
Does that sound convoluted? I'm not sure how else to do this but Excel scripting may be the only way to go, if someone can help program the logic of the search and row-finding that would be awesome.
I can write you something to use from within Excel but because you already have the csv files you can just process them from the command line. Copy the following code and save it into a file with the name "ProductReport.vbs". If your csv file is named myfile.csv you can use it as follows from the command line:
ProductReport widget1 myfile.csv
Output is to the console. If this isn't quite what you want let me know and I'll tweak it.
'fso is used for file operations 'arg is for accessing command line arguments set fso = CreateObject("Scripting.FileSystemObject") set arg = Wscript.Arguments 'if a file name is not specified show command syntax and exit if arg.Count <> 2 then wscript.Echo "ProductReport product csvfile" wscript.Quit end if 'the product you are looking for is the first argument. 'the csv file name is the second argument product = arg(0) csvfile = arg(1) if not fso.FileExists(csvfile) then wscript.Echo "could not find file",csvfile wscript.Quit end if 'open the csv file and process it one line at a time 'I am assuming that thformat of the file is strictly as you specified 'where all lines but the customer line start with a comma set tso = fso.OpenTextFile(csvfile) 'add a comma to the start and end of the product so we don't accidentally 'confuse two products. for example, without the commas, "widget1" would 'match both "widget1" and "widget12" match = "," & product & "," 'I know that the first line contains the column headers and will be 'taken as a customer but because the next line contains a new '(valid) customer this won't matter. wscript.Echo "scanning",csvfile,"for",product wscript.Echo do until tso.AtEndOfStream line = tso.ReadLine() if left(line,1) = "," then if instr(1,line,match,vbTextCompare) > 0 then wscript.Echo product,"->",cust end if else parsed = Split(line,",") cust = parsed(0) & " " & parsed(1) end if loop tso.Close
I was bored so I did the same thing but from within Excel. The disadvantage is that the code must be added to every workbook that you want to run it against. The vbs version is run from outside the Excel environment and is not part of the workbook. Please note that this code was written in Excel 2003. Your environment may differ. To add the embedded code, do the following:
1) load the data file into Excel
2) select menu items Tools -> Macro -> Macros
3) enter ProductReport as the macro name
4) click Create
5) replace the displayed code (all of it) with my code
6) save the workbook
To run, press Alt-F8 then select the ProductReport macro
Sub ProductReport() ' This macro scans the first sheet in the current workbook for all customers who ' have placed an order for a product which is entered at the prompt when the macro ' is run. The resulting report is copied into spare cells on that same worksheet. ' The location of the report is determined by the values of oRow and oCol. From ' the sample data it appears that the end of the customer data can be detected ' when both columns 1 and 4 are both blank. ' ' 2011-04-15 - JdG - original code Product = InputBox("Product:", "Enter product to search for") 'product name Match = LCase(Product) 'name in lower case iRow = 2 'the starting input row oRow = 2 'the starting output row (for report data) oCol = 8 'the starting output column With Worksheets(1) '1 = first worksheet 'clear out any existing report data r = oRow Do Until .Cells(r, oCol) = "" .Cells(r, oCol + 0) = "" .Cells(r, oCol + 1) = "" .Cells(r, oCol + 2) = "" r = r + 1 Loop 'keep going until we have a row with nothing in both the first name and product columns Do Until .Cells(iRow, 1) = "" And .Cells(iRow, 4) = "" 'if first column is not blank then this is a customer row - save the row number 'otherwise check the product column and display the info if a product match 'note that all comparisons are done in lower case If .Cells(iRow, 1) <> "" Then cRow = iRow Else If LCase(.Cells(iRow, 4)) = Match Then .Cells(oRow, oCol + 0) = .Cells(cRow, 1) 'copy the customer first name .Cells(oRow, oCol + 1) = .Cells(cRow, 2) 'copy the customer last name .Cells(oRow, oCol + 2) = .Cells(iRow, 4) 'copy the product name .Cells(oRow, oCol + 3) = .Cells(iRow, 5) 'copy the prodoct amount oRow = oRow + 1 'select next output row End If End If iRow = iRow + 1 Loop End With End Sub