# How do I populate unique cells from one worksheet to another based on a reference field?

#### edmund1985

##### New Member
Hi all,

I'm trying to come up with a formula that will populate unique cells in a new worksheet by referencing another worksheet that contains all the data.

So far I've used an INDEX MATCH formula, but that takes across all cells and displays the contents of the desired ones, while producing #N/A for the others.

Example

The data set looks something like this:

 Unique ID Colour 101 White 102 White 103 Red 104 White 105 Red 106 White

<tbody>
</tbody>

The formula I'm using is below:
=INDEX('Sheet1'!A2,MATCH("White",'Sheet1'!B2,0))

And the result in Sheet 2 looks like this:

 Unique ID 101 102 #N/A 104 #N/A 106

<tbody>
</tbody>

I'd like if the '#N/A' cells weren't returned at all, i.e. for it to look like this:

 Unique ID 101 102 104 106

<tbody>
</tbody>

Does anyone know if that's possible?? Thanks in advance for any help!!!

#### wideboydixon

##### Well-known Member
This seems to be a popular question; I see it many times per day

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

WBD

#### edmund1985

##### New Member
Hi WBD,

Thanks for taking a look at it. I tried using your formula but it's producing blank cells unfortunately... I removed the IFERROR function to get a better idea of what's happening and it's giving me #VALUE!

Is there another way of doing it?? Maybe with different formula combinations?

Thanks!
Ed

#### wideboydixon

##### Well-known Member
Well, you'll need to change the cell references etc.

{=IFERROR(INDEX(\$A\$2:\$A\$7,SMALL(IF(\$B\$2:\$B\$7=\$E\$2,ROW(\$B\$2:\$B\$7)-ROW(\$B\$1)),ROW()-1)), "")}

\$A\$2:\$A\$7 : Specifies all the values in the Unique ID column
\$B\$2:\$B\$7 : Specifies all the values in the Colour column
\$E\$2 : Specifies the cell that contains the criteria or you could hard-code this to "White"
\$B\$1 : Specifies the Colour header cell (i.e. the one that contains "Colour")
ROW()-1 : This is the tricky one. My first value was in D2 so this returns the values 1,2,3,4,5 etc. You might need to change this to return 1 in the first cell you put the formula

WBD

#### edmund1985

##### New Member
Hi WBD,

I actually got it to work perfectly with the simple example I gave

It's given me a few correct results for my actual dataset, although it's missed out certain records sequentially and then at random from various places in the column

I should mention that my actual dataset is over 7500 entries long, and the actual unique ID field values are more like:

 412361863-1 412568432-1 412568432-2 412357123-1 420535415-1 420957135-1 420957135-2 420957135-3 412693515-1 etc.

<tbody>
</tbody>

Could that be affecting it??

1,082,167
Messages
5,363,528
Members
400,747
Latest member
monty_gl

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...