0

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
nehshere
  • 5
  • 5
  • I don't see the use of `OFFSET` in your formula. But one way to make a range reference dynamic, is to use a Table. If that doesn't work for you, please provide examples of what you are doing, what you have tried, and your desired results. Suggest you read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld Jul 24 '17 at 18:14
  • @RonRosenfeld please see my example above with an image and updated formula. I don't think I will be able to use a Table as I need to get this data from another sheet. My file is already heavy and using tables might make it more bulky and slower. If you think that won't be the case then please do advise on how should I go about it. Thanks, Neha. – nehshere Jul 24 '17 at 18:56
  • You can do this either by using the Remove Duplicates feature, or using the Advanced Filter feature. Is there any particular reason you need a formula to do this instead of using the built-in features specifically for this purpose? – tigeravatar Jul 24 '17 at 19:08
  • @tigeravatar I have built an automated template in excel for account structure generation in bulk. Users just have to enter a list on one of the sheets and the rest of the workbook updates itself. There are various sheets in the workbook and some are dependent on the others. I want to minimize the number of steps users take to get the output in the desired format. Hence I need to use formulas. I cant use the Advanced Filter feature as that can only be used if I am deduping the list on the same sheet. – nehshere Jul 24 '17 at 19:16

1 Answers1

0

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60