Thanks so much. This was a huge help.
I thought it was so useful, I wrote an Excel macro to run the steps noted above to make it easier for others (note: I'm not a VBA programmer so it may not be the most efficient way, but it does the job).
To use it, complete Steps 1-3 as noted above; then, instead of Step 4-11, do the following (then save as noted in Step 12):
Go to Tools > Macro > Visual Basic Editor
From Visual Basic Editor menu:
Go to Insert > Module
Paste the following in the Module1 (Code) window, then click the Play button at the top of the Visual Basic Editor (or select Run > Run Sub/UserForm):
Sub CSVFixer()
'
' CSVFixer Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
' Delete first column
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
' Delete new columns
Columns("E:N").Select
Selection.Delete Shift:=xlToLeft
' insert 3 new columns before Column C
' for Foil, Condition, and Language
Columns("C:E").Insert Shift:=xlToLeft
Range("C1").Select
ActiveCell.FormulaR1C1 = "Foil"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Condition"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Language"
' rename columns
Range("F1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Edition"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Count"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Count"
' remove extra text from card name (e.g., "Golgari Guildgate (a)" or "(b)")
Dim fndA As Variant
Dim rplcA As Variant
Dim fndB As Variant
Dim rplcB As Variant
fndA = " (a)"
rplcA = ""
fndB = " (b)"
rplcB = ""
'Perform the Find/Replace All
Cells.Replace what:=fndA, Replacement:=rplcA, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Cells.Replace what:=fndB, Replacement:=rplcB, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Dim lRow As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row
' Find the last non-blank cell in row 1
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
' fill Condition column
Range("D2", Cells(lRow, 4)).Value = "Near Mint"
' fill Language column
Range("E2", Cells(lRow, 5)).Value = "English"
' Copy worksheet and rename
Worksheets(1).Copy After:=Worksheets(1)
Sheets(1).Name = "Regular"
Sheets(2).Name = "Foil"
' fill Foil column in "Foil" worksheet
Sheets(2).Range("C2", Cells(lRow, 3)).Value = "foil"
' delete Foil count from Regular worksheet
' delete Regular count from Foil worksheet
Sheets(1).Columns(2).EntireColumn.Delete
Sheets(2).Columns(1).EntireColumn.Delete
' delete header row on copied ("Foil") worksheet
Sheets(2).Rows(1).EntireRow.Delete
' Get row number for inserting new rows from "Foil" worksheet
Dim newRow As Long
newRow = lRow + 1
' Copy "Foil" rows to original ("Regular") worksheet
Sheets(2).Range("A1", Cells(lRow, lCol)).Copy Sheets(1).Range("A" & newRow)
' Delete copied ("Foil") worksheet
Sheets(2).Delete
End Sub
Note that when the copied "Foil" worksheet is deleted, you will be prompted to confirm the Delete action.
You can also save the macro as a .bas file and import it to use again any time.
I hope this saves some time for some folks.
