+ Reply to Thread
Results 1 to 4 of 4

part of array formula based on data change in another column?

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    part of array formula based on data change in another column?

    I have the following two worksheets and am trying to retrieve the ItemID in the "New" worksheet from the "Item-Group" worksheet.

    The first few items in column C on the "New" worksheet tab are correct, but I want to fill in the rest of the table (which is far longer in practice).

    Column C's formula is
    Please Login or Register  to view this content.
    , entered as an array formula, and then copied down the column. The problem is, for C4, the formula has ROW(A3), but since that's where group b starts in the column, I want it to go back and restart at ROW(A1). Is there a nice way to do this?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: part of array formula based on data change in another column?

    Try

    =IFERROR(INDEX('Item-Group'!$A$2:$A$25,SMALL(IF('Item-Group'!$C$2:$C$25=New!A2,ROW($A$2:$A$25)-ROW($A$2)+1,""),ROWS(COUNTIF($A$2:A2,A2)))),"")

    Enter with Ctrl+Shift+Emter

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: part of array formula based on data change in another column?

    That gives the correct ItemID for the first instance where the group name changes, but the in between ones are just the same as the first one.

    That may not be clear.

    What I want is this:
    a-2-43
    a-2-45
    b-6-841
    b-6-842
    b-6-894
    b-6-895
    b-6-896
    b-6-897
    c-8-843

    but your formula is showing this:
    a-2-43
    a-2-43
    b-6-841
    b-6-841
    b-6-841
    b-6-841
    b-6-841
    b-6-841
    c-8-843

  4. #4
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: part of array formula based on data change in another column?

    Oh, I think I found it - you don't want ROWS in the last part, just the COUNTIF statement.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Help - Automatic website navigation page change based on column data change
    By cbatten01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2015, 08:00 PM
  2. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  3. You cannot change part of an array
    By Paul H765 in forum Excel General
    Replies: 5
    Last Post: 09-27-2012, 03:33 PM
  4. Replies: 4
    Last Post: 09-05-2012, 01:50 PM
  5. Cannot change part of an array?
    By money n da sank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2008, 01:12 PM
  6. How can I change a # in an array formula for a whole column?
    By gswegan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM
  7. [SOLVED] warning: cannot change part of an array. how do I by pass this?
    By Gwyneth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2005, 07:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1