comparison - Filter Records In An Excel Spreadsheet By Matching Numbers In Another Spreadsheet -

comparison - Filter Records In An Excel Spreadsheet By Matching Numbers In Another Spreadsheet -

i have website logs (tab-delimited text files) contain constituent info (name, e-mail, etc...). import logs raiser's edge database using importomatic. importomatic give me exception log shows records didn't imported. note isn't question raiser's border or importomatic (although nice if importomatic exception log had info didn't imported, instead of line number).

example website log:

importid keyind firstname lastname orgname addrimpid addrinfosrc prefaddr addrsendmail addrlines addrcity addrstate addrzip addrcountry conscode phoneaddrimpid2 phonenumber2 phonetype2 interneti201303999 joshua jackson interneta201303999 net yes yes 194 notch ln. reeds spring mo 65737 united states of america partner interneta201303999 jj@anti-exe.com e-mail

example exception log:

line 1: info long field: max length = 3 state [object: 'cconstitaddress', pk: '-1', import id: '', desc: '194 notch ln., reeds spring', field: 'state', value: 'missour'] -->bbreapi.crecordclass.save()-->importom.cimport.process()

in particular instance, misspelled missouri (purposefully) resulted in exception.

i import website log 1 worksheet , exception log worksheet (same file). excel gives website log line numbers (my record line 2, because header line 1). import exception log space-delimited (i guess utilize :-delimited, well) text file (my exception line 1, doesn't import header). should able formula strip : (=left(b1, len(b1)-1)) , add together 1 (=b1+1), have column of line numbers should match line numbers excel gave website log. if there no way match exception's line number column excel's created line numbers, create additional column , have fill series of numbers.

what i'm wanting is, somehow, utilize exception log's line x filter show records need manually entered. i'm thinking reverse-filter (hiding info , showing bad data).

obviously, haven't gotten working (or else wouldn't ask). going wrong? i'm sure can done, it's eluding me.

if list of errors on sheet2 in cola (one line per cell, no need split on ":"), can add together column imported info using formula:

=iserror(match("line " & row()-1 & ":*",sheet2!a:a,0))

output:

true --> imported false --> had error

excel comparison