I have a very large table from which i am extracting a unique list using a combination of =countif =and index(match,match).
It works fine but I am sure there is a better, more efficient way of writing it that uses less processor resources. I do not want to resort to an array formula.
Any ideas? I've attached a sample.
I see an INDEX MATCH MATCH formula, no COUNTIFS. What's wrong with what you have got? There won't be any more efficient formula than that. I think you are going to have to be a bit more explicit about what the perceived problem is.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Have you considered filter, copy, paste?
The last row of data is missing from your sample extraction, is that intentional?
By changing the formula in column X of the data sheet slightly, you can omit the exact match criteria, and use the more efficient approximate match to find the required row instead.
If your real data matches your sample, in the sense that the data and extract tables contain the exact same column headings then you can use an approximate match there as well.
Sample reattached with the changes made.
Note that the method used on the extract sheet only works with the column headings sorted in ascending order (will work descending if you set the optional match argument to -1).
The method on extract (2) will work with the columns in any order (sorted, or unsorted), but they must be in the same order on both the data and extract sheets.
edit:- removed attachment with errors, revised version attached to post #6
Last edited by jason.b75; 06-02-2018 at 12:20 PM.
in your index and match you could replace the match for the columns with "column()-1" if your colomnheaders are the same.
Not really sure what you are trying to do here, either.
1 thing I noticed is that your INDEX range starts in row 4, but your 1st MATCH starts in row 5 - that will cause incorrect answers.
what are you trying to do on Data in col X?
This would give the same results...
=COUNTIF($W$5:W5,$X$4)
But they are not unique results, they are progressive counts of the same ID
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ford's observations prompted me to check my suggestion, I found a couple of errors which I have now corrected.
I noticed the missing row, but checked my results against the original formula results, not the source data
Hi AliGW
The =countif is in red on the data sheet. The problem as I see it with the countif is if anyone deletes a row it falls over.
The obvious answer to that - dont delete rows, just delete data.
Also, that is not a countif(), it is just adding stuff up
=IF(W6=$X$4,1,0)+X5
Hi FDibbins
The numbers in column X identify in ascending order the lines of data that match the users' selection. The Index match uses that as its row reference in the index match
Yes, I understand that, but it is still not a COUNTIF(). I gave you the COUNTIF equivalent in post #5.
You still have not yet explained what you are trying to do though
(and did you correct the range in your INDEX/MATCH yet?)
Absolutely. However the data is undated by some of our 'less IT-savvy members' so someone will at some point delete. I am reluctant to use protection because of issues that can cause.
The =countif creates a numerically ascending list of the lines that match the User's selection. The index match routine uses that column as one part of the match functions
I have now corrected the typo.
When the User selects the code in A9 on the Extract sheet, the countif formula checks the data and if it matches if adds 1 to the above and that is used by the index match etc etc.
On the extract sheet the User sees every line from the Data sheet that matches the User's selection.
Repeating Ford's previous comment, there is no countif in your sheet.Originally Posted by BRISBANEBOB
I've attached another updated copy of your sample, this time actually using countif as Ford suggested.
I didn't bother with that previously as the original question mentioned nothing of problems with deleting rows, which made it unnecessary.
Hi
The countifs are in the data sheet in column X but actually necessary. A simpler formula could be used it test the contents of column W
Last edited by BRISBANEBOB; 06-02-2018 at 08:45 PM.
OK whether there is actually a countif or not - what exactly are you trying to achieve here, and why do you think the way you are doing it, is not the best way?
It gets very frustrating trying to talk to somebody who will not listen.
Formula from your sample Data worksheet, column X
=IF(W6=$X$4,1,0)+X5 This is a simple sum and logical test, fails immediately when rows are deleted, it is not a counitf!
Formula (suggested by Ford) that I added into your sample sheet and reattached to post #13
=COUNTIF(W$4:W5,$X$4) This is a countif formula, failure by deleting rows almost impossible.
Try looking at the revised version in post #13 and let us know if there is anything wrong with that one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks