CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
nehshere
nehshere

Reputation: 5

Ignore Duplicates and Create New List of Unique Values in Excel with offset

My question is related to a question asked earlier, but as I am a new member I was not able to comment on that question.

The earlier question asked how we can dedupe a list in a workbook to create a new list of unique values on another sheet in the same workbook. The top voted solution given by @achaudhr works for me but in that I need to specify the exact cells the formula needs to reference. In the comments on that answer @Dan has mentioned that we must use OFFSET if we are referring to a dynamic range.

This is the formula I am using at the moment:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

I have tried using offset with this formula but I guess I am doing something wrong because it keeps giving me #N/A as a result.

If my data was in column A (as per the above formula), I want to be able to change the ":$A$20" part of the range dynamically. The list in column A changes as per an input I put in the workbook on another sheet (let's call it Sheet 3). Hence I cannot hardcode the cells in the index formula range or else I have to change this range every time my list updates.

See the sheet 1 and sheet 2 layout example by clicking this link for an image

Based on the above layout, the cell in E2 calculates the max cell number for the list in column A on sheet 1. This number changes when the input in Sheet 3 changes. I edited the above formula to use OFFSET to reference E2 in the following way:

B2=INDEX(OFFSET('Sheet 1'!$A$1,'Sheet 1'!$E$2,0), MATCH(0, COUNTIF($B$1:B1, OFFSET('Sheet 1'!$A$1,'Sheet 1'!$E$2,0)), 0))

This formula is returning #N/A (and I did press Ctrl + Shift + Enter so its not because of that).

I hope the group here can help me solve this. Look forward to the inputs and thanks for all your help.

Thanks, Neha

Upvotes: 0

Views: 2486

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

The way to use OFFSET in a dynamic range determining formula, where it is column length that varies, is to use that value as the [height] parameter.

So, in the case of your example, the formula would look like:

B2: =IFERROR(INDEX(OFFSET($A$1,1,0,$E$2-1), MATCH(0, COUNTIF($B$1:B1, OFFSET($A$1,1,0,$E$2-1)), 0)),"")

Reference: $A$1 (could also set this at $A$2 with a 0 Row offset

Row Offset: 1 (since A1 contains the header)

Column Offset: 0

[height]: Contents of $E$2 minus 1 (since we are not including the header in the list)

[width]: left blank

Upvotes: 0

Related Questions