• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I detect, count or list all missing records

P

persol

New Member
Hello, I am confronted with a file with over 1000 records of invoice numbers and customer numbers, I would like to find out if there is a formula that could detect the missing invoice numbers or the missing customer numbers. Please note that these numbers are all system generated and are therefore sequentially issued. Is there a way I can do it with an Excel formula?


Your support will be greatly appreciated.

Persol
 
Hi, persol!

Supposing you've got your over 1K numbers in column A, sorted in ascending order.

You can build column B with the first (lower) number of A in B1, from B2 in advance just add 1 to previous B.

In cell C1 type this:

=SI(ESERROR(BUSCARV(B1;A:A;1;FALSO));"Falta";"")

(no english translation as I assume you're using Excel in spanish, aren't you?)

Copy down from C2 to last row used in B.

And then check one by one, or filter column C selecting "Falta".

Regards!
 
Hi, it helps a lot, however, my keyboard and software follow the Canadian version - all is in English. I gather the meaning of all the words , except the word buscar which means to find but i would appreciate the english version (it is not the "find" is it?) . I tried to replace it as follows but did not work.

=ifI(ESERROR(BUSCARV(B1;A:A;1;FALSe));"Falta";"")
 
Hi, persol!

Related to your second question and supposing you have in columns A thru C the data of Company/VendorCode/MailAddress, in column D just type:

=CONTAR.SI(A:A;A2)>1

If a company only has one vendor and/or one mail address, it'll just have one entry in A column. Otherwise, you get 'VERDADERO' in column D.

Regards!
 
Hi, persol!

I assumed spanish, from now on I'll deliver english version too.

Translation pending:

=SI(ESERROR(BUSCARV(B1;A:A;1;FALSO));"Falta";"") -----> in english: =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"Missing","")

Regards!
 
Hi SirJB87,


I came across a snag. That is, if the address is missing a letter or the address is incomplete it gets ignored. Is there a way that a formula can read at least part of the address or find a word that is the sane in other addresses?


For instance: if i had "1230 Pensilvania Ave." on column A and "1230 Pensilvania Avenue" or "1230 Pensilvania" in column B. From this example you could infer that these three addresses are related and perhaps are the same and would be nice to highlight for further review and follow up. That is the very objective I was looking to do.


Can it be done?
 
Hi, persol!


You're talking about data normalization or standardization, and that's absolutely out of the scope the formulas I wrote before.

What you can try is copying the mail address column to a new one and work on this. How?


Well, there's no magic formulas, just many simple ideas:

- eliminate redundant (or all) spaces

- replace "St", "St.", "Street", and any possible variation for the one chosen (or even for nothing)

- same for "Av"

- same for "Floor", "Suite", "Office", and whatsoever you consider

- I don't know which is the format por Floor and Office, but act accordingly


After this first step, order the whole worksheet by this new column, and look how it feels. If not acceptable, then analyze it better. And retry.


If you want to upload the file, maybe I'd give you other advises.

Instructions for uploading files: http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards!
 
Hi SirJB7,


I thank you very much for the follow up. I did what you suggested and helped a great deal, but it is moving very slow. I was also looking into the "sounds like" formula my boss asked me to look into. I have never heard of such string, did you?
 
Hi, persol!

There are transformations that might be accomplished when you normalize data, that are called "sounds like" type. That means to equal or make equivalent different strings that in a determinated language are pronounced or sound alike.

For example in spanish, "ola/hola" (wave/hello), in portuguese "posso/poço" (can/hole), in english "to/two/too" or "by/buy/bye".

It's a huge amount of work just far beyond what you should do in Excel automatically, but as you're talking about ONLY 1000 over records, my humble advise is...: just do it manually, first define the criteria for the common parts (Ave, St, Floor, etc...), and then type.

Otherwise tell your boss to hire a data normalization external service.

Regards!
 
You must log in or register to reply here.
Back
Top