Need Help- Dupe records Index/ Match function

rrenzi

New Member
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

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

Well-known Member
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
Since you have Excel 365 maybe try the FILTER function.

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

Replies
1
Views
37
Replies
2
Views
73
Replies
1
Views
68
Replies
0
Views
874
Replies
3
Views
50

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.

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

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