Vlookup issue - duplicate data

**Deanoz** · 09-17-2012, 12:38 AM

So i have spent a good three days surfing the next and trying to figure a formula for my data extract.

Currently, im running a report for staff whereby survey data is returning duplicate values. My problem that I have is that I want it to be "pluggable". I can just extract data, paste it in and it will calculate everything. Below is my screen shots.. I believe it is doable with a pivot but would rather it done with a formula..

So basically, the return for a vlookup when choosing the "NAME" will return each incident individually, but because the data extract has the name more than once, it will not return any results or just the one result...

Any ideas?

Cheers

data extract.jpg

Untitled.jpg

**dilipandey** · 09-17-2012, 12:59 AM

Hi Deanoz,

Welcome to the forum.

This is achievable using combination of few functions in array formula, please upload a sample workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Deanoz** · 09-17-2012, 01:31 AM

File attatched

**dilipandey** · 09-17-2012, 01:51 AM

Hi Deanoz,

Use the below formula:-

Formula:

{=IFERROR(INDEX('Survey Analysis'!$B$1:$B$578,SMALL(IF('Survey Analysis'!$A$1:$A$578='Template Name'!$F$4,ROW('Survey Analysis'!$B$1:$B$578),""),ROW('Template Name'!$A1)),1),"")}

See attached:-

Book1(2).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**Deanoz** · 09-17-2012, 06:25 PM

Thanks DiliPandey but im still not getting the correct results. I have added the formula and it seems to be an error returning any results..

Please see new attatchment. Thank you in advance

http://www.2shared.com/file/wQp4WoRR/test.html

**FDibbins** · 09-17-2012, 06:30 PM

1 way would be to add a helper column, and then concatenate the name and the incident number? and then base your lookup on that?

**Deanoz** · 09-17-2012, 06:34 PM

could that be done when the data within the spreadsheet is constantly changing? It is using vlookup from numerous data sheets to return a result to one spreadsheet. then using that to identify results?

**Deanoz** · 09-17-2012, 08:37 PM

To be honest, id rather the formula set up ready.. if you look at how i got the end results currently, i dont think concatenation will work?

**FDibbins** · 09-17-2012, 08:52 PM

the concatenation will work, even if the data changes. you can hide the column with that data in it if you want. im at home right now and only have 2003, so i cant open .xlsx files, sorry

**dilipandey** · 09-18-2012, 02:01 AM

Hi Deanoz,

Thanks DiliPandey but im still not getting the correct results.

Are you entering the formula using Ctrl Shift Enter key combination ? Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Deanoz** · 09-18-2012, 06:10 PM

Yeah but its still blank in the field.. no info is returning

http://www.2shared.com/file/wQp4WoRR/test.html see attatched

**dilipandey** · 09-19-2012, 01:13 AM

Hi Deanoz,

I am not able to download the file from the link you mentioned...
Suggest you to upload the file here in the forum.

Click on go advanced while reply and look for paper clip icon to upload. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Vlookup issue - duplicate data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Re: Vlookup issue - duplicate data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions