Find(/lookup) next value

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This will get what you want into the list. I hope somebody else will be able to adapt it for you to get your list into the order you want (revenue large to small).

In A5 confirmed with CTRL+SHIFT+ENTER and copied down:

=IFERROR(INDEX(Data!$A$3:$A$20, SMALL(IF(COUNTIFS(Return!$B$1, Data!$E$3:$E$20, Return!$B$2, Data!$D$3:$D$20, Return!$B$3, Data!$C$3:$C$20), ROW(Data!$F$3:$F$20)-MIN(ROW(Data!$F$3:$F$20))+1), ROW(A1)), COLUMN(A1)),"")

In B5 copied down:

=IFERROR(VLOOKUP($A5,Data!$A$3:$F$20,2,0),"")

In C5 copied down:

=IFERROR(VLOOKUP($A5,Data!$A$3:$F$20,6,0),"")

Thank you for the rep!

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

Thanks for clarifying. If you had not responded here, then nobody would have come up with anything more, as it would have appeared as if you had left the thread hanging. It's always best to be very clear in the thread whether what you have been offered as a solution works and to what extent you might want it adapting.

Hi,

I believe this will return the layout you want.

If you have Power Query available to you, it should be possible to make this more flexible.

Originally Posted by xlnitwit

Hi,

I believe this will return the layout you want.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Of course, I quite understand.

An additional ranking column is added to the data sheet (truncated due to post length):
Excel 2010 32 bit

	A	B	C	D	E	F	G
2	Number	Name	Location	Industry	Department	Revenue	Rank
3	1	Mazda	1	shop	right	10	=IF(F3="",COUNT($F$3:$F$20)+1,RANK(F3,$F$3:$F$20))+COUNTIF(F$2:F2,""&F3)
4	2	Audi	3	garage	left	20	=IF(F4="",COUNT($F$3:$F$20)+1,RANK(F4,$F$3:$F$20))+COUNTIF(F$2:F3,""&F4)
5	3	Land Rover	5	street	up		=IF(F5="",COUNT($F$3:$F$20)+1,RANK(F5,$F$3:$F$20))+COUNTIF(F$2:F4,""&F5)

Sheet: Data

and then the layout on the Return sheet is this:
Excel 2010 32 bit

	A	B	C	D	E	F
1	List - Department	right
2	List - Industry	shop
3	Location	1
4	Number	Name	Revenue (sort by largest)	Counter	Rank (DESC)	Position
5	=IF($F5="","",INDEX(Data!A$3:A$20,$F5))	=IF($F5="","",INDEX(Data!B$3:B$20,$F5))	=IF($F5="","",INDEX(Data!F$3:F$20,$F5))	=COUNTIFS(Data!$E$3:$E$20,Return!$B$1, Data!$D$3:$D$20, Return!$B$2,Data!$C$3:$C$20,Return!$B$3)	=IF(ROWS(D$5:D5)>D$5,"",SMALL(IF(Data!$E$3:$E$20=B$1,IF(Data!$D$3:$D$20=Return!$B$2,IF(Data!$C$3:$C$20=Return!$B$3,Data!$G$3:$G$20))),ROWS(E$2:E2)))	=MATCH(E5,Data!$G$3:$G$20,0)
6	=IF($F6="","",INDEX(Data!A$3:A$20,$F6))	=IF($F6="","",INDEX(Data!B$3:B$20,$F6))	=IF($F6="","",INDEX(Data!F$3:F$20,$F6))		=IF(ROWS(D$5:D6)>D$5,"",SMALL(IF(Data!$E$3:$E$20=B$1,IF(Data!$D$3:$D$20=Return!$B$2,IF(Data!$C$3:$C$20=Return!$B$3,Data!$G$3:$G$20))),ROWS(E$2:E3)))	=MATCH(E6,Data!$G$3:$G$20,0)

Sheet: Return

That's a lot of helper columns!

I notice there are duplicates in the ranking list on the data tab: is this not potentially a problem with a longer list?

I have in fact just corrected the ranking formula so there should be no duplicates in it.

Yes, that works now.

I seem to have an 'inverted' interpretation.

In C5 this array entered

Formula:

Please Login or Register  to view this content.

Then in A5 array entered, filled across column B and down.

Formula:

Please Login or Register  to view this content.

You have marked the thread as solved, so it's unlikely that FlameRetired will see your request (unless he has subscribed to the thread).

All you need to do is copy the formulae given into the cells stated in his post and confirm each with CTRL+SHIFT+ENTER.

Here - I've done it for you.

The least processor consuming is applying Excel's built-in options.
Use a 'smart' table (not a pivot table).

Originally Posted by henrikf1

For the record, the workbook contains about 7000 rows, and therefore the least process consuming approach will be best. It may well be that the last post is a better fit...
FlameRetired - could you please attach a workbook with your solution?

Thanks in advance.

henrikf1

Sorry about the late reply. My ISP suddenly went down yesterday afternoon ... (upgrades).

The file is attached.


You hadn't mentioned anything about the 7000 lines before.

You are correctly concerned about this. Array formulas are resource hungry. Let me know how this works and I will see what I can do at my end to make this less so.

Any solution that I come up with will likely require helper columns to avoid the array formulas. Are you OK with that?

P.S. Edit I just now looked at snb's solution. For what it's worth, IMHO that looks like the better solution by far. (Wish I'd had thought of it myself. LOL )

Originally Posted by AliGW

Here - I've done it for you.

AliGW,

Thanks for the backup. I hadn't noticed until I posted. Support is appreciated.

Are you expecting further contributions to this thread? If so, then you really need to remove the SOLVED tag from the first post.

Are you now saying that FlameRetired's proposed solution is not working for you? If so, then you need to make it clear in what way it isn't working. What is different in your real data sheet that is making it fail? Can you provide a sample sheet that is a better representation of the real data? We can't second guess potential hurdles if you are only giving us half the picture!

henrikf1,

I too thought you were finished.

This upload has a helper column in column G of Data. It returns Revenue unique to the 3 criteria.

Formula:

Please Login or Register  to view this content.

I copied it down to row 7500 in an attempt to simulate what you expect to be the input and then some.

While none of the following formulas require Ctrl + Shift + Enter they are still array formulas. With the sample data they perform well. Please let me know how they perform on live data.

In C5 of Return this formula for the sorted unique Revenue.

Formula:

Please Login or Register  to view this content.

I copied this and the next two formulas down to row 7000. Names returned by this formula in B5 and filled down.

Formula:

Please Login or Register  to view this content.

In A5 to return the numbers.

Formula:

Please Login or Register  to view this content.