Finding missing data with IF and count etc, maybe INDEX? Duplicates.

[NJS1982]

Hi all, hoping somebody can help me with this.

I have a big list of organisation names, populating column B. Some of these are companies, some are not.
When they are companies, I have provided a relevant company number.
However, I have some gaps (as in, the name appears twice in my list, but only one line has a company number).
To avoid having to manually trawl the data, I am sure there must be a formula to work out if
A) There is a duplicated name and
B) If there is a corresponding company number already there which should be used

The below example table may help bring it to light?!
- Organisation 1, 5, 7, 8 and 10 are NOT companies, so correctly have no company number present.
- Organisation 2,3,4,6 and 9 ARE companies, so have the company number populated
- Company 6 has a company number (987654) which is present for three of the four entries; therefore is there a formula to check if a number exists for an existing company in the dataset and then populates that return?

	Company Number <tbody> </tbody>
Organisation Name 1 <tbody> </tbody>	- <tbody> </tbody>
Organisation Name 2 <tbody> </tbody>	123456 <tbody> </tbody>
Organisation Name 2 <tbody> </tbody>	123456 <tbody> </tbody>
Organisation Name 3 <tbody> </tbody>	234566 <tbody> </tbody>
Organisation Name 4 <tbody> </tbody>	345678 <tbody> </tbody>
Organisation Name 5 <tbody> </tbody>	-
Organisation Name 6 <tbody> </tbody>	987654 <tbody> </tbody>
Organisation Name 6 <tbody> </tbody>	987654 <tbody> </tbody>
Organisation Name 6 <tbody> </tbody>	987654 <tbody> </tbody>
Organisation Name 6 <tbody> </tbody>	???
Organisation Name 7 <tbody> </tbody>	- <tbody> </tbody>
Organisation Name 8 <tbody> </tbody>	-
Organisation Name 9 <tbody> </tbody>	678909 <tbody> </tbody>
Organisation Name 10 <tbody> </tbody>	- <tbody> </tbody>
Organisation Name 10 <tbody> </tbody>	- <tbody> </tbody>

<tbody>
</tbody>

 

[Nutster]

To find duplicates, try using conditional format - Duplicates and then filter on colour to show only the records that are coloured because of the duplicate records.

Do you have a list of Companies and the corresponding numbers somewhere? If so, this can be accomplished with a fairly simple VLOOKUP.

Let's say you have a list of Companies and their Company numbers in a worksheet called Companies.

Company Name	Company Number
ABC	105242
JKL	412241
PQR	241244

<tbody>
</tbody>

etc.

In the next sheet (Sheet1), you have column B with company names, and you want the corresponding company number in column C.

=IFERROR(VLOOKUP($B2, Companies!$A:$B, 2, False), "")

Copy and paste this formula down all of column C as far as you have names.

The VLOOKUP looks for the value from B2 in the first column of the range in the second argument (Companies!$A:$B) and returns the value in column 2. The False tells VLOOKUP to check all names to find a value. If the value from B2 can not be found in the Companies!A, then it returns #N/A error. The IFERROR function looks at the value from its first argument; if the result is an error, it returns the second argument. If the first argument is not an error, it returns that value.