Locating text in a column, then adding numbers from the adjacent column.

[CertainlyIndecisive]

I'm an excel novice making a daily assignment sheet for my staff based on the number of procedures in different areas.

One column of the sheet contains the number of procedures.
The column next to it will contain the names (non-case sensitive text) of staff.
Rows represent different work locations.

I need to be able to find all matching names that will be manually entered down the column and add the number adjacent to their name.

(The idea is to auto-tally each individual's workload that is manually entered in)

Let me know if I need to clarify things further, and I appreciate any help!

[AliGW]

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[CertainlyIndecisive]

Ok, I made a SUPER generic sheet showing what I want to do.

I highlighted the stuff I want generated in yellow. Let me know if there's anything else. Thank you so much!

[63falcondude]

I think some of your sums were off.

Try these:

A17 =INDEX(B$2:B$12,MATCH(0,COUNTIF(B$2:B$12,"<"&B$2:B$12)-SUM(COUNTIF(B$2:B$12,"="&A$16:A16)),0)) Ctrl Shift Enter

B17 =SUMIF(B$2:B$12,A17,C$2:C$12)

[johnmpl]

Hi, to all!

If you want a non-cse formula for A17, try this little tweak:
=INDEX(B$2:B$12,MATCH(,INDEX(COUNTIF(B$2:B$12,"<"&B$2:B$12)-SUMPRODUCT(COUNTIF(B$2:B$12,A$16:A16)),),))

Blessings!

[johnmpl]

Hi again!

I leave two options in the file attached.

Option 1: Formulas --> Same than before.
Option 2: Power Query (Free add-in Excel 2010 and newer).

Blessings!

[CertainlyIndecisive]

63falcondude, yours worked flawlessly - and yes, my sums were probably off... one of the reasons I'm making this sheet to minimize careless errors. Haha.

johnmpl, I'm working hard to learn the syntax, but I'm not really understanding the reason for/against a non-cse formula (which I assume is the combined logic in a single function by hitting CTRL Shift Enter).

This has been super helpful so far. I'm struggling to make it all come together the way I want with my real excel sheet I'm working with. If I manage to screw things up, I'll be back for more clarification. Thanks!

[63falcondude]

Glad we could help.

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

[CertainlyIndecisive]

Well, I'm definitely terrible at excel. I tried modifying my own sheet, and a few things are tripping me up. Names are fake, and all identifiers have been removed.

I'm required keep a certain format layout for consistency in appearance while transitioning from the old paper method of doing assignments.

Calculated section is highlighted green.

I've got a few issues:

1) I've managed to screw up the "total procedures" section. Procedures aren't being added correctly, and I keep screwing it up more as I mess with it.
2) I'm not sure how to make an exception to skip (necessary) blank cells on the column containing names
3) Is there a way to make cells remain blank instead of pumping out a "#N/A" when all names have been used? Staffing number varies constantly.

Once again, thank you for all the help.

[AliGW]

See if this helps:

Excel 2016 (Windows) 32 bit

	I	J
5	=IFNA(INDEX(D$5:D$32,MATCH(0,COUNTIF(D$5:D$32,"<"&D$5:D$32)-SUM(COUNTIF(D$5:D$32,"="&I$4:I4)),0)),"")	=IF(I5="","",SUMIF(D$5:D$32,I5,G$5:G$32))

Sheet: Assignment

The first of the formulae needs array entering, of course, then drag copy both down.

[CertainlyIndecisive]

The Total Procedures column is now working perfectly. Awesome!

However, the Names column is only outputting "#NAME?" for all cells with the change you suggested. I made sure to do CTRL+SHIFT+ENTER.

Am I missing something here?

[Glenn Kennedy]

OMG Ali.... that's a bit hard on the eyes...

[CertainlyIndecisive]

Trust me, I cringe at the layout as well - but my staff will not adopt it unless it looks the same as their paper sheets.

[AliGW]

OMG Ali.... that's a bit hard on the eyes...

As is the workbook it came from ...

[AliGW]

Am I missing something here?

Maybe, maybe not. Try this instead:

=IFERROR(INDEX(D$5:D$32,MATCH(0,COUNTIF(D$5:D$32,"<"&D$5:D$32)-SUM(COUNTIF(D$5:D$32,"="&I$4:I4)),0)),"")

[AliGW]

Trust me, I cringe at the layout as well - but my staff will not adopt it unless it looks the same as their paper sheets.

Your staff are blackmailing you? Who's the boss???

[CertainlyIndecisive]

As is the workbook it came from ...

Are you inferring there was a previous version stored in that file with identifiers? I pre-emptively deleted it to ensure I stay within forum rules.

In either case, your last suggestion fixed everything and looks perfect. I appreciate it!

[AliGW]

I am not inferring anything. Nor am I implying (which is what I think you meant) anything about identifiers, about which I know nothing. I was merely referring to the green background.

Glad to have helped, anyway.

[CertainlyIndecisive]

Your staff are blackmailing you? Who's the boss???

I just marked this thread as solved, and I caught one more issue. If any of the cells of the manually entered names are empty, the calculated section for names and procedures outputs "0" for every cell until every cell for names has something entered in it.

It would be nice to see what the procedure totals are even when all the names haven't been completely entered. Is that possible?

[AliGW]

Post your latest version of the file showing these errors, and I'll take a look.

[CertainlyIndecisive]

Post your latest version of the file showing these errors, and I'll take a look.

See attachment. Remove any name from the yellow highlighted cells, and the calculations all become "0". Every cell must have something in it to work, so I guess it needs some kind of excluding criteria for an empty cell.

[AliGW]

I have to go offline now - sorry. I will look in again tomorrow morning and help out if nobody else has stepped in.

[AliGW]

OK, well I have tried various versions of the array formula that I know about, and this is the only one that I can make work in your workbook with blanks in the source data (not sure why). It's a bit of overkill, but it does the job. In I5:

=IFERROR(INDEX($D$5:$D$32, MATCH(MIN(IF(ISBLANK($D$5:$D$32)+COUNTIF(N1:$N$1, $D$5:$D$32), "", IF(ISNUMBER($D$5:$D$32), COUNTIF($D$5:$D$32, "<"&$D$5:$D$32), COUNTIF($D$5:$D$32, "<"&$D$5:$D$32)+SUM(IF(ISNUMBER($D$5:$D$32), 1, 0))+1))), IF(ISBLANK($D$5:$D$32)+COUNTIF(N1:$N$1, $D$5:$D$32), "", IF(ISNUMBER($D$5:$D$32), COUNTIF($D$5:$D$32, "<"&$D$5:$D$32), COUNTIF($D$5:$D$32, "<"&$D$5:$D$32)+SUM(IF(ISNUMBER($D$5:$D$32), 1, 0))+1)), 0)),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

[Glenn Kennedy]

i thought that I had a few solutions that were one-liners; but they mostly seem to collapse if there are a large number of blanks. Ali's seems to work OK. So does this. It's still a bit of a monster, but it's a baby monster (again an array formula):

=IFERROR(INDEX($D$5:$D$32,MATCH(SMALL(IF(IFERROR((MATCH($D$5:$D$32,$D$5:$D$32,0)=ROW($D$5:$D$32)-MIN(ROW($D$5:$D$32))+1),FALSE),COUNTIF($D$5:$D$32,"<="&$D$5:$D$32)),ROWS(I$4:I4)),COUNTIF($D$5:$D$32,"<="&$D$5:$D$32),0)),"")

in I5, array entered and copied down

[johnmpl]

Hi, to all!

Another option, in I5, could be (CSE Formula):
=IFERROR(INDEX(D$5:D$32,MATCH(,COUNTIF(D$5:D$32,"<"&D$5:D$32)+9^9*(D$5:D$32="")-SUM(COUNTIF(D$5:D$32,I$4:I4)),)),"")

And drag it down. Blessings!

[Glenn Kennedy]

I like it!!!

[FlameRetired]

@ johnmpl

Yes. Very clever.

[AliGW]

I like it!!!

Just seen it and so do I!

[Glenn Kennedy]

TbH... I'm still not confident that I understand it fully... But it works and it's filed away..

[AliGW]

Ditto and ditto.