Hi all, quite an involved one here so please bear with me.
Working at a company trying to rectify their orders. They have a weekly report that they are seeking a comparator on, to see the changes. Because so many of their orders are very similar, I have taken their data and had to create a field for a concatenated URN: Concatenate(A,B,C,D)
on a processing sheet, I then have a list of these URNs from the week 1 and week 2 sheets in columns A and B, using =IFERROR(SOB_ThisWeek[URN],"") and =IFERROR(SOB_LastWeek[URN],"") respectively.
In column C, I wanted to find all the Duplicate values, and ended up with the array formula:
{=IFERROR(INDEX($A$2:$A$1499,SMALL(IF(COUNTIF($B$2:$B$1499,$A$2:$A$1499)>0,ROW($A$2:$A$1499)-1,9999999999),ROW()-1)),"")}
In D, I wanted *all* URNs and after an hour of swearing, I found:
{=INDIRECT(TEXT(MIN(IF(($B$2:$B$1324<>"")*(COUNTIF($D$1:D1,$B$2:$B$1324)=0),ROW($2:$1324)*100+COLUMN($B:$B),7^8)),"R0C00"),)&""}
All good so far. With this, I was able to pull up all the *none-duplicate* values with a sneaky =IF(ISNA(MATCH(D2,Dupes,0)),D2,"") in column E
If you are still with me, I love you, because here's the actual issue:
Column F, I am trying to pull back the 'A' value for that URN. Because I don't know if this URN is for the current or previous week, I am trying to perform an index match based on the value that the none-duplicate column has returned:
=IF(LOOKUP($E2,$A:$A),INDEX(SOB_TW[Part],MATCH(TRUE,INDEX(SOB_TW[URN]=$E2,0),0)),INDEX(SOB_LW[Part],MATCH(TRUE,INDEX(SOB_LW[URN]=$E2,0),0)))
I have tried this both arrayed and none-arrayed, and this is off the back of attempting a Vlookup in the same style, but where the none-duplicate column has returned a value, the formula returns a #Value !
Any help greatly appreciated. (and yes, this thing does take like 5 mins to run the full series of calculations - It's a once a week report, so boss is not too fussed)
Best,
Morgan
Working at a company trying to rectify their orders. They have a weekly report that they are seeking a comparator on, to see the changes. Because so many of their orders are very similar, I have taken their data and had to create a field for a concatenated URN: Concatenate(A,B,C,D)
on a processing sheet, I then have a list of these URNs from the week 1 and week 2 sheets in columns A and B, using =IFERROR(SOB_ThisWeek[URN],"") and =IFERROR(SOB_LastWeek[URN],"") respectively.
In column C, I wanted to find all the Duplicate values, and ended up with the array formula:
{=IFERROR(INDEX($A$2:$A$1499,SMALL(IF(COUNTIF($B$2:$B$1499,$A$2:$A$1499)>0,ROW($A$2:$A$1499)-1,9999999999),ROW()-1)),"")}
In D, I wanted *all* URNs and after an hour of swearing, I found:
{=INDIRECT(TEXT(MIN(IF(($B$2:$B$1324<>"")*(COUNTIF($D$1:D1,$B$2:$B$1324)=0),ROW($2:$1324)*100+COLUMN($B:$B),7^8)),"R0C00"),)&""}
All good so far. With this, I was able to pull up all the *none-duplicate* values with a sneaky =IF(ISNA(MATCH(D2,Dupes,0)),D2,"") in column E
If you are still with me, I love you, because here's the actual issue:
Column F, I am trying to pull back the 'A' value for that URN. Because I don't know if this URN is for the current or previous week, I am trying to perform an index match based on the value that the none-duplicate column has returned:
=IF(LOOKUP($E2,$A:$A),INDEX(SOB_TW[Part],MATCH(TRUE,INDEX(SOB_TW[URN]=$E2,0),0)),INDEX(SOB_LW[Part],MATCH(TRUE,INDEX(SOB_LW[URN]=$E2,0),0)))
I have tried this both arrayed and none-arrayed, and this is off the back of attempting a Vlookup in the same style, but where the none-duplicate column has returned a value, the formula returns a #Value !
Any help greatly appreciated. (and yes, this thing does take like 5 mins to run the full series of calculations - It's a once a week report, so boss is not too fussed)
Best,
Morgan
