Excel function that copies rows if their value > 10

[Biggy]

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:

Client List Example:

Overdue Invoice List Example:

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

 

[Steve_R]

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

	A	B	C	D	E	F	G	H	I	J	K
Contact Name	Contact Phone	Contact E-Mail	Office E-Mail
Josh Ganan	(811) 112-1616	josh-ganan@bedf.com	info@bedf.com
AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com	office@axcb.com
Aaron Duner	(530) 514-3344	aarond@ldjgj.com	help@ldjgj.com
AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com	office@axcb.com

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Invoice #[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Client #[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Invoice Sum[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Buy Date[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Due date[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Paid[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Days Due[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AB10001[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]$517[/TD]
[TD="align: center"]2/01/2016[/TD]
[TD="align: center"]16/01/2016[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]AB10002[/TD]
[TD="align: center"]Client 2[/TD]
[TD="align: center"]$921[/TD]
[TD="align: center"]23/01/2016[/TD]
[TD="align: center"]6/02/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]AB10003[/TD]
[TD="align: center"]Client 3[/TD]
[TD="align: center"]$1211[/TD]
[TD="align: center"]27/01/2016[/TD]
[TD="align: center"]10/02/2016[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]AB10004[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]$1351[/TD]
[TD="align: center"]29/01/2016[/TD]
[TD="align: center"]12/02/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]19[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]AB10005[/TD]
[TD="align: center"]Client 4[/TD]
[TD="align: center"]$1321[/TD]
[TD="align: center"]1/02/2016[/TD]
[TD="align: center"]15/02/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]16[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]AB10006[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]$955[/TD]
[TD="align: center"]3/02/2016[/TD]
[TD="align: center"]17/02/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]14[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]AB10007[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]$365[/TD]
[TD="align: center"]6/02/2016[/TD]
[TD="align: center"]20/02/2016[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]AB10008[/TD]
[TD="align: center"]Client 5[/TD]
[TD="align: center"]$419[/TD]
[TD="align: center"]9/02/2016[/TD]
[TD="align: center"]23/02/2016[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]AB10009[/TD]
[TD="align: center"]Client 6[/TD]
[TD="align: center"]$946[/TD]
[TD="align: center"]12/02/2016[/TD]
[TD="align: center"]26/02/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]AB10010[/TD]
[TD="align: center"]Client 3[/TD]
[TD="align: center"]$1000[/TD]
[TD="align: center"]20/02/2016[/TD]
[TD="align: center"]5/03/2016[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]-3[/TD]

</tbody>

Invoice_List
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF($G2>=OverdueDays,INDEX(Client_List!$A:$K,MATCH($B2,Client_List!$A:$A,0),MATCH(H$1,Client_List!$1:$1,0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]OverdueDays[/TH]
[TD="align: left"]=Variables!$A$1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[My Aswer Is This]

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.

 

[Biggy]

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

 

[Steve_R]

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).

 

[My Aswer Is This]

I understand and am just watching to see how this Index Match works out.

 

[Steve_R]

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.

 

[Biggy]

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

 

[Steve_R]

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.

 

[Biggy]

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

 

[My Aswer Is This]

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.