Hi ,
It looks like you're using an ad blocker.



The revenue generated from the adverts on the site is a critical part of our funding - and it's because of these ads that I can offer the site for free. But using the site for free AND blocking the ads doesn't feel like a great thing to do, which is why this box is so large and inconvenient. Some sites will completely block your access, but I'm not doing that - I'm appealing to your good nature instead. Did you know that you can allow ads for specific sites, whilst still blocking them on others?

Thanks,
Ian Williams aka Fetch
or for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers
GlennR
GlennR
Nov 2019
2:20pm, 28 Nov 2019
46,592 posts
  • Quote
  • Pin
GlennR
Array...
larkim
larkim
Nov 2019
2:47pm, 28 Nov 2019
9,612 posts
  • Quote
  • Pin
larkim
I guess the clue's in the name.

Stepping through it logically, I'm guessing it effectively performs multiple individual calculations through arrays of data to output a result - so multiple results on the way to a final result? Though I don't think that's correct, otherwise sumproduct would need a CSE wouldn't it?
larkim
larkim
Nov 2019
2:49pm, 28 Nov 2019
9,613 posts
  • Quote
  • Pin
larkim
(Edit - just googled to find out that sumproduct is an array function / formula, but just doesn't need CSE. Now that's confusing!)
Diogenes
Diogenes
Nov 2019
3:05pm, 28 Nov 2019
42,853 posts
  • Quote
  • Pin
Diogenes
Here's a poser for you.

I have a column of document numbers in column A
There are 114 rows of data
Each row has the name of 1 or more people who need to approve each document, up to a maximum of 9 approvers, each in their own column

From this I want to create a report grouped by person, listing each document they need to approve

I'm trying to automate this. Any ideas on the best way to manipulate the data to get this?
GregP
GregP
Nov 2019
3:18pm, 28 Nov 2019
116,424 posts
  • Quote
  • Pin
GregP
Also did anyone see the puzzle in this week's New Scientist?

Nine columns with an approver in them, or nine columns one for each approver?
Diogenes
Diogenes
Nov 2019
3:20pm, 28 Nov 2019
42,854 posts
  • Quote
  • Pin
Diogenes
Nine columns with approvers in them
An approver could be in any columns, i.e. column C against one doc but column B for another and E for another
larkim
larkim
Nov 2019
3:25pm, 28 Nov 2019
9,616 posts
  • Quote
  • Pin
larkim
Sample data?
Doc Approver Approver2 Approver3
A Albert Bertie
B Bertie Charlie
C Albert Bertie Charlie
D Charlie Albert
E Bertie
F Charlie Bertie

Reporting as
Approver
Albert - A, C, D
Bertlie - A, B, C, E, F
Charlie - B, C, D, F
Diogenes
Diogenes
Nov 2019
3:28pm, 28 Nov 2019
42,855 posts
  • Quote
  • Pin
Diogenes
Yes, that's it
Diogenes
Diogenes
Nov 2019
3:51pm, 28 Nov 2019
42,857 posts
  • Quote
  • Pin
Diogenes
=INDEX(A2:A114,MATCH(L2,B2:B114,0)) find the first instance, where L2 is the name of the approver.

Now to try and get it to return all instances.
Diogenes
Diogenes
Nov 2019
4:04pm, 28 Nov 2019
42,858 posts
  • Quote
  • Pin
Diogenes
I've named my arrays, but that returns an N/A output

=INDEX(DOCS,MATCH(L2,NAMES,0))

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...
  • Show full description...

Related Threads

  • excel
  • support
  • tech
  • work








Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 114,014 Fetchies!
Already a Fetchie? Sign in here