Excel function that copies rows if their value > 10

Biggy

New Member
Joined
Feb 14, 2014
Messages
22
Hello Excel specialists,

I got number of sheets. In one I have list of invoices and relevant information about them. Second sheet is clients information list and third is list with overdue payments. Below I added screenshots to better understand situation:

Invoice List Example:
Invoice%20list_zpsuicjjorr.jpg


Client List Example:
Buyer%20list_zpsmjl50tu0.jpg


Overdue Invoice List Example:
Whats%20necessary_zps0nslplv0.jpg


I need to auto index-match rows from the Invoice list who are overdue more than 10 days. I know that index-match should be used, but I don't now exactly how. How formula changes in case if it's necessary to set day range, e.g. 5-10?

Thank You and hopefully someone will be able to help.

Sincerely,
Tony
 
If I'm interpretting you correctly, you can't start off with nothing in a sheet and use Index & match or any other function to get all expiring invoices. The VBA method I would use for that would be:
- clear Overdue_Invoices;
- set a filter records <= OverdueDays (named range);
- copy filtred records to Overdue_Invoices;
Either before or after copy of records to Overdue_Invoices use VBA to
- generate relevant formula (whether on Invoice list or Overdue Invoices would depend on needs);
- copy formula to required range.

In other words, filtering would still be involved in the method I would use (does not mean someone else won't have a solution that works better for you).
VBA can be used to change filters (for example when someone activates a sheet filters can be refreshed). Alternatively, Macro buttons can be used to toggle filters on/off.

To change vlookup to Index and Match. Actually, in the example below Index, Match & Match.

The formula
=IF($G2>=OverdueDays,INDEX(Client_List!$A:$K,MATCH($B2,Client_List!$A:$A,0),MATCH(H$1,Client_List!$1:$1,0)),"")

Hint before tackling the explanation: Writing separate components of nested formula in separate cells then copy/paste the parts together the work far easier than the result makes it looks;)

Hopefully, the following explanation of that formula is easily understood.

This formula is designed to drags across and down and responds to changes of column heading (i.e. rows are not hard-coded making it a more dynamic formula).

Breaking that down to help you understand Index & Match:
If $G2>= OverdueDays I am presuming you understand If().

Microsoft's Index function reference is INDEX(array, row_num, [column_num])

Another way to think of that is INDEX(Where, which row, which column)

Client_List!$A:$K, tells Index the lookup range.
MATCH($B2,Client_List!$A:$A,0) tells index the lookup row number
MATCH(H$1,Client_List!$1:$1,0) tells index the lookup column number.

Now for the Match parts:

Microsoft's Match function reference is MATCH(lookup_value, lookup_array, [match_type])

Match(what value, where, type of match required)

MATCH($B2,Client_List!$A:$A,0)
In this case, we are looking for the value contained in B2 which contains the client reference (column $B fixed to enable drag copy to other columns).
The client reference is contained in Client_List! column $A:$A (column $A fixed to enables drag/copy to other columns)
Match type 0 tells Match we are looking for an exact match.

The same logic applies in MATCH(H$1,Client_List!$1:$1,0) except we are looking to match the heading in H1 to the Headings in Client list to find the relevant column number. NOTE As with client reference, column names need to be an exact match (I always copy/paste column headings when using this method).

Result

Excel 2010
ABCDE
FG
HIJK
1Invoice #Client #Invoice SumBuy DateDue datePaidDays DueContact NameContact PhoneContact E-MailOffice E-Mail
2AB10001Client 1$5172/01/201616/01/2016Y0
3AB10002Client 2$92123/01/20166/02/2016N25Josh Ganan(811) 112-1616 josh-ganan@bedf.cominfo@bedf.com
4AB10003Client 3$121127/01/201610/02/2016Y0
5AB10004Client 1$135129/01/201612/02/2016N19AJ Gilbert(532) 326-2181aj.gilbert@axcb.comoffice@axcb.com
6AB10005Client 4$13211/02/201615/02/2016N16Aaron Duner(530) 514-3344aarond@ldjgj.comhelp@ldjgj.com
7AB10006Client 1$9553/02/201617/02/2016N14AJ Gilbert(532) 326-2181aj.gilbert@axcb.comoffice@axcb.com
8AB10007Client 1$3656/02/201620/02/2016Y0
9AB10008Client 5$4199/02/201623/02/2016Y0
10AB10009Client 6$94612/02/201626/02/2016N5
11AB10010Client 3$100020/02/20165/03/2016N-3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Invoice_List

Worksheet Formulas
CellFormula
H2=IF($G2>=OverdueDays,INDEX(Client_List!$A:$K,MATCH($B2,Client_List!$A:$A,0),MATCH(H$1,Client_List!$1:$1,0)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
OverdueDays=Variables!$A$1

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Client list is as per previous post
The named range OverdueDays contains the value 10










 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I have been following this thread from the beginning and thought an easy five lines of Vba code could do all you want. But in post #1 you said “I know that index-match should be used”. A Vba filtering script could do all you want in 3 seconds and all you would need to do is press a button. But then I understand a lot of people do not like using Vba.

I'm following this tread because I do not know much about Index Matching so I'm hoping to learn something here myself.
 
Upvote 0
Thank You a lot Steve! Lot of things became more clearer now!
To go little further: how would be necessary to improve formula so it takes only 4 rows with a data (3,5,6,7) and shows it all in the new sheet, so in that case I would see it in the new clearer sheet only invoices who are already overdue?

Sincerely,
Biggy
 
Upvote 0
I have been following this thread from the beginning and thought an easy five lines of Vba code could do all you want. But in post #1 you said “I know that index-match should be used”. A Vba filtering script could do all you want in 3 seconds and all you would need to do is press a button. But then I understand a lot of people do not like using Vba.

I'm following this tread because I do not know much about Index Matching so I'm hoping to learn something here myself.


My recommendation is always to work with a single set of data and avoid VBA if possible (but I ignore my own recommendations for personal convenience or through laziness:(). Duplication sometimes opens up opportunities for people to work with a copy while thinking they are working with the main table. However, we can't make the decision whether or not it is necessary to generate an expired invoice sheet. The OP needs to make that decision with the full understanding of the project.

A lot of web samples use Index & Match but I am not sure how often users are made aware of Index, Match & Match, which is far better option for advanced users (but it is big step for a novice user).
 
Upvote 0
The advantage of that formula over Vlookup or simple Index & Match is you can add new columns or change column data extracted by changing column headings. Lock up your formulas and you can hand that over to end users if they have different needs from the same lookup table.

The problem I still have helping you progress is knowing whether or not you need assistance to use VBA to create another sheet or whether you think an automated filter might achieve what you need. As you would realise from my response above, duplicating data across different sheets is something I prefer to avoid but I don't fully understand your project and can't guide you further on that. You need to make make the decision. Using VBA you can use a macro button to switch a filter on/off, relieving end users of the task (and ensuring they filter correctly). The other thing I would need to know is whether you created a named ranged for expired days and what you named it.
 
Upvote 0
Hello Steve,
I can see that You are super super excel user, but for me lot of things is first, so really aprreciated Your help. About days - not, they hasn't any names anywhere.
Reason why I am trying to get that index-match answer is somehow smple - as I said, it would help in other situations, e.g. in different forms to get data who are withinh some kind of range or exact value, e.g. best example is invoice who is in excel example files. I just want to understand how it exactly works.

About VBA: If You are up to it, maybe You can make it, cause in that case I will have one more option + VBA I can try to learn something from so comments are more than welcome here.

Sincerely,
Biggy
 
Upvote 0
I can see that You are super super excel user

That is flattering to the ears but far from reality. I am long on experience in some areas of Excel and through pig-headed determination and occasional help from others solved all problems I faced over the years. That does not make me a super-user.

Do you want to code that will work as a toggle to filter data in place or do you need it copied to another sheet for some reason. I strongly suggest you create a named range for overdue days which gives your or end users the ability to change to 15, 5, 100 or whatever else is needed at the time without touching code or formulas. using a named range makes formulas easier to read.
 
Upvote 0
Hello Steve,
Toggle would be nice, but as well it could be helpful for some other time. All the questions I ask and responses I get ,I try to learn from and implicate them also in other occasions. It's not only - ok, got an answer, problem is solved, thanx, up to next one. I am really trying to understand it all, so possibility to copy a code would be nice addition.

Sincerely,
Tony
 
Upvote 0
Your quote:
It's not only - ok, got an answer, problem is solved

So what answer did you get that worked for you. You should share it here so others can see what worked.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top