+ Reply to Thread
Results 1 to 9 of 9

INDEX + MATCH with Duplicate values

  1. #1
    Registered User
    Join Date
    02-07-2021
    Location
    United Kingdom
    MS-Off Ver
    2013 / GoogleSheets
    Posts
    9

    INDEX + MATCH with Duplicate values

    Hi ExcelForum Friends,

    I have been working with a formula to transpose data from one sheet to another in a different layout.

    My raw data holds duplicate values within the data and i can't seem to retrieve these values after the initial entry.

    Could anyone give me some guidance on how to resolve this, please see the formula i'm using below.

    =IFERROR(INDEX(MERGED!B$2:B$11,MATCH(0,INDEX(--(COUNTIF(B$1:B1,MERGED!B$2:B$11)=4),0),)),"")

    If needed i can share a simplified file.

    Thanks
    Last edited by D.Wizard; 02-27-2021 at 06:31 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: INDEX + MATCH with Duplicate values

    Wizard,

    Sample file would be appreciated with few rows of data, alongside what you want the actual output to look like, please?

    Ochimus

  3. #3
    Registered User
    Join Date
    02-07-2021
    Location
    United Kingdom
    MS-Off Ver
    2013 / GoogleSheets
    Posts
    9

    Re: INDEX + MATCH with Duplicate values

    Hi Ochimus,

    Thank you for getting back to me, please see attached

    Wizard
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: INDEX + MATCH with Duplicate values

    A2, copied to B2 & down;
    =IF(ROWS(A$2:A2)>4*COUNTA(MERGED!$A$2:$A$20),"",INDEX(MERGED!A:A,AGGREGATE(15,6,ROW(MERGED!$A$2:$A$20),1+INT((ROWS(A$2:A2)-1)/4))))

    C2, copied down:
    =IF(A2="","",INDEX(MERGED!$C$1:$F$1,,1+MOD((ROWS(C$2:C2)-1),4)))

    D2, copied down:
    =IF(A2="","",INDEX(MERGED!$C$2:$F$20,1+INT((ROWS(D$2:D2)-1)/4),1+MOD((ROWS(D$2:D2)-1),4)))

    see file.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: INDEX + MATCH with Duplicate values

    Wizard,

    You don't need formulae, just a Pivot Table of your Output, as shown on the attached?

    If you add, change or delete anything in the Output worksheet, right click any cell in the Pivot and select "Refresh".

    Ochimus
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: INDEX + MATCH with Duplicate values

    try below formula
    a2=IFERROR(INDEX(MERGED!$A:$A,MATCH(B2,MERGED!B:B,0)),"")
    C2=IF(B2="","",INDEX(MERGED!$C$1:$F$1,MOD(ROWS(C$2:C2)-1,4)+1))
    D2=IFERROR(INDEX(MERGED!$C:$F,MATCH(B2,MERGED!B:B,0),MOD(ROWS(C$2:C2)-1,4)+1),"")
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: INDEX + MATCH with Duplicate values

    Hi,
    changed your formula a little:

    =IFERROR(INDEX(MERGED!B$2:B$11,MATCH(0,INDEX(--(COUNTIF(A$1:A1,MERGED!A$2:A$11)=COUNTIF(MERGED!$A$1:$J$1,"*SHOP*")),0),)),"")
    Attached Files Attached Files
    Last edited by Limor_OP; 02-27-2021 at 03:14 PM.

  8. #8
    Registered User
    Join Date
    02-07-2021
    Location
    United Kingdom
    MS-Off Ver
    2013 / GoogleSheets
    Posts
    9

    Re: INDEX + MATCH with Duplicate values

    Thank you all for the support guys. Worked a treat

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: INDEX + MATCH with Duplicate values

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Avoiding Duplicate Values when using INDEX/MATCH
    By CharlieVictorEcho in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2023, 03:45 PM
  2. Index Match duplicate values without gaps or repeat values?
    By russwongg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-26-2018, 09:27 PM
  3. Match and Index with Duplicate values
    By shortracer5 in forum Excel General
    Replies: 1
    Last Post: 09-09-2016, 05:25 AM
  4. Index Match & Duplicate values
    By Shaulyt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2016, 05:23 PM
  5. [SOLVED] Sum Duplicate Index-match-match Values
    By djh30 in forum Excel General
    Replies: 3
    Last Post: 01-06-2015, 11:27 PM
  6. Index Match with Duplicate Values
    By js2178a in forum Excel General
    Replies: 2
    Last Post: 09-28-2014, 02:21 AM
  7. [SOLVED] Index and match functions for duplicate values
    By JBERK in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-23-2012, 10:48 AM

Tags for this Thread

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