Need Help- Dupe records Index/ Match function

rrenzi

New Member
Joined
Aug 13, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
What I need to do. I download a list of 'special' subscribers. I need to write a formula that will transfer these subscribers to a sheet for each group. A subscriber can have one product or multiple products. A group may have zero, one or multiple 'special' subs. I have to write a formula and I can not copy and paste even though that might be easier... lol. There is a one to one association of subno, groupno and name. A sub can not be in multiple groups. Each group has only one name. I am using the Index Match function, but it is not working right when I have multiple members with more than one product. Hopefully the link to my spreadsheet works

 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,400
Office Version
  1. 2016
Platform
  1. Windows
Hi RRenzi,

I'm not sure if I'm following correctly but I believe your use of the SMALL formula in "AB CO" A33 is working correctly but you're having problems retrieving the other columns using just INDEX & MATCH?

I'd suggest adding a column for "Info Row" (let's say column Q) populated with your
{=SMALL(IF(($B$1=Info!$X$5:$X$1000), MATCH(ROW(Info!$X$5:$X$1000), ROW(Info!$X$5:$X$1000)), ""),ROWS($BA$1:$BA1))}
then all the other columns become just INDEX & MATCH using that row number, e.g.
A33 would become =INDEX(Info!J$5:J$1000,$Q33)
B33 would become =INDEX(Info!K$5:K$1000,$Q33)
etc.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
Since you have Excel 365 maybe try the FILTER function.
So on your ABC sheet:

=FILTER(Info!$J$5:$X$1000,Info!$X$5:$X$1000=$B$1)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,069
Messages
5,628,468
Members
416,319
Latest member
k8o2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top