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?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]Company Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]123456[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]123456[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl71, width: 118"]234566[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl71, width: 118"]345678[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]987654[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]987654[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]987654[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl71, width: 118"]678909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl71, width: 141"]Organisation Name 10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl72, width: 118"]-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.[TABLE="width: 300"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Company Number[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]105242[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD]412241[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]241244[/TD]
[/TR]
</tbody>[/TABLE]
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.