Excel 2007 : Index with Duplicates

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

dummy updates.xlsx

this is a dummy version of what I'm working on, does this help?

the goal is to be able to change the drop down menu located in raw announcements and have it auto populate into that department's tab without the fields in the A columns of either sheets needing to be distinct.

Have a look... a change on the "master" sheet so you can just enter any department on any and flag it as a "manager" message.

Each department sheet is now identical. You can create a new department sheet by simply copying one of the existing sheets and renaming the sheet. It's the name of the sheet that triggers the value in A1 causing those items to be collected.

The array formulas on the sheets do the heavy lifting.

=IFERROR(INDEX('Raw Announcements'!$C$1:$C$100, SMALL(IF(INDEX('Raw Announcements'!$A$1:$A$100&'Raw Announcements'!$B$1:$B$100=$A$1, 0), ROW('Raw Announcements'!$C$1:$C$100), ""), ROW(A1))), "")


The INDEX is making a single reference list of the values in A1:A100 & B1:B100 on the Raw sheet. So in the example sheet, since A3=Claims and B3=Manager, that creates a string ClaimsManager. A4=Claims and B4=(blank) so the string created is just Claims. And so on. The array strings those 100 pairs of cells together.

The IF() function that is wrapped around the index above tests each of those strings created above to see if the string created matches the value in cell A1 on that sheet. So on the Claims sheet, it would make a match on rows 4, 12, and 14-100, all are just "claims", so they would all endup in the array. {4,12,14,15,16,17,etc....}

The SMALL() function that is wrapped around that is used to grab one value at a time out of that array. SMALL(array, k). For "k" we start with ROW(A1) which translates to 1, so SMALL takes the first value in the array, in this case 4.

The final step is the outer INDEX() function. We made an index of the entire column C on the Raw sheet, now we want the value in the "row" that matches the value the SMALL() returned. The first value it found was 4 (row 4), so it returns the 4th value in column C... which is the message "Never Mind". So that is the FIRST result you get in B3 with that initial formula.

Now, when B3 is copied DOWN to B4, all the parts of the formula stay the same except the final ROW(A1) changes to ROW(A2). Row(A2) translates to 2, so now the SMALL() array will spit out the second smallest number, in this case 12. The 12th value in column C of RAW is "Message10".

Now you see how it works.
============================

The second section of array formulas starts again at B11 on the Claims sheet and the formula is almost identical, except we add the word "Manager" to the value in A1 to create "ClaimsManager", and the inner INDEX is now creating an array of rows that resolve to that instead of just "Claims". Other than that, all is the same.

can't seem to find your non-working formula anywhere. I need to see your edits to see where it is breaking.

Also, Array formulas are confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will see enclosing braces { } appear around your formula to indicate an active array. Maybe that's all you missed.

Here's an example where a row# added to the column A makes it so only one array formula is needed on each row of the named sheet(s).

Thanks so much, I thought I had an error but I figured it out, silly program forgot a space in someone's name.

You are a lifesaver.

This is formula:

=IFERROR(INDEX('MS Agents wk'!A$1:A$300, SMALL(IF('MS Agents wk'!$B$1:$B$300=$A$1, ROW('MS Agents wk'!$B$1:$B$300), ""), ROW($A1))), "")

...notice there are no braces? Excel adds those when you press CTRL-SHIFT-ENTER as you enter the formula above in cell A3. Then you copy across and down 20-30 rows, no more than needed.

Also, I noticed you changed the formula from evaluating 100 rows to evaluting an entire column... 65k rows! Never do that with an array. Decide how many rows you're going to need, add a buffer, and use that.

After enter the formulas, of course, we had to fix the formatting of the data appearing on the Green, Al sheet, and I also went into to FILE > OPTIONS > ADVANCED (scroll down a bit) and UNcheck the [x] Zero Values option. (Maybe Tools > Options > View on Excel 2003).

Once you've made those changes and then copied the first row formulas down 20-30 rows, copy that sheet and rename the copies to make sheets for other people.

Array formulas are calc-intense, so try to keep the number of cells holding them down to only a required number. If you're going to be adding TONs of data on the main sheet, then we might want to also eliminate a good chunk of these array formulas on the names sheets. We could do that easily if there were a single column on your data sheet that had a unique value on every row. If you inserted an empty column A and just added a row # to number each row uniquely, we could change the name sheets to only having an array formula in one column, then all the rest of the formulas could be VLOOKUP and the workbook would stay very robust.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

Array for B3:

=IFERROR(INDEX('Raw Announcements'!$C$1:$C$10, SMALL(IF(INDEX('Raw Announcements'!$A$1:$A$10&'Raw Announcements'!$B$1:$B$10=$A$1, 0) + INDEX('Raw Announcements'!$A$1:$A$10&'Raw Announcements'!$B$1:$B$10="All", 0), ROW('Raw Announcements'!$C$1:$C$10), ""), ROW(A1))), "")

Array for B11:

=IFERROR(INDEX('Raw Announcements'!$C$1:$C$10, SMALL(IF(INDEX('Raw Announcements'!$A$1:$A$10&'Raw Announcements'!$B$1:$B$10=$A$1&"Manager", 0) + INDEX('Raw Announcements'!$A$1:$A$10&'Raw Announcements'!$B$1:$B$10="AllManager", 0), ROW('Raw Announcements'!$C$1:$C$10), ""), ROW(A1))), "")


Copy down, the copy to other sheets, then delete the ALL sheet.