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

 

[DILIPandey]

Biggy,


Screenshots are not there in your post.
Suggest you to copy paste data directly from Excel.


Regards,
DILIPandey

 

[Biggy]

Hello,

Thank You a lot for suggestion. I am trying to get going MrExcel htmlcreator, but something isn't right, but for now on I will post data directly in the post.
About this issue: I received message from one person who will help me with this problem, so I will not post data directly, but thank You for the suggestion!

Sincerely,
Biggy

 

[Steve_R]

I suggest you avoid the third sheet an use a formula that returns a result only if your required time is met. You can filter on due days if you wish to hide blanks and could copy or print as needed.

The formula below uses G2>=10 to get invoices 10 days overdue and older. Better practice would be to use a cell reference so you can set this external to your formula. Example: =IF(G2>=Variables!A1,VLOOKUP(B2,Client_List!A:K,9,FALSE),""). Better still, use a named range. Example =IF(G2>=OverdueDays,VLOOKUP(B2,Client_List!A:K,9,FALSE),"")

If you compare formulas, you will see the part that changes to extract different column numbers.

=IF(G2>=OverdueDays,VLOOKUP(B2,Client_List!A:K,9,FALSE),"")

If you need results on separate sheets, you can copy/paste or VBA code can be written to extract relevant results to a new sheet. I would be happy to do this but suggest VBA is best avoided when possible. As it makes a project harder to follow, especially for someone else taking over your role.



Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
Contact Name	Contact Phone	Contact Email	Company email
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"]24[/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"]18[/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"]15[/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"]13[/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"]4[/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"]-4[/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>=10,VLOOKUP(B2,Client_List!A:K,9,FALSE),"")
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(G2>=10,VLOOKUP(B2,Client_List!A:K,10,FALSE),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=IF(G2>=10,VLOOKUP(B2,Client_List!A:K,11,FALSE),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=IF(G2>=10,VLOOKUP(B2,Client_List!A:K,8,FALSE),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Steve_R]

I am trying to get going MrExcel htmlcreator, but something isn't right, but for now on I will post data directly in the post.
Biggy

Post your HTML Maker issue in the following part of the forum
About This Board

 

[Biggy]

Thank You a lot for all Your help!
I Will use named ranges in the future, but 3rd sheet in real life worksheet is much better solution. What will change in the formula If I would like to directly extract rows who are overdue (without using any filtering options)? Wouldn't be better to use index-match combination?

Sincerely,
Tony

 

[Steve_R]

Forgot to post the lookup table for those who could not view the attachments

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
office@axcb.com	AJ Gilbert	aj.gilbert@axcb.com
info@bedf.com	Josh Ganan	josh-ganan@bedf.com
help@hjklf.com	Simon Jarvish	sjarvish@hjklf.com
help@ldjgj.com	Aaron Duner	aarond@ldjgj.com
info@jgopd.com	Judith Larson	judith.larson@jgopd.com
help@eufjkg.com	Jeff Raz	jraz@eufjkg.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: #BDD7EE, align: center"]Client #[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Company Name[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Address[/TD]
[TD="bgcolor: #BDD7EE, align: center"]City[/TD]
[TD="bgcolor: #BDD7EE, align: center"]State[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Zip Code[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Office Phone[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Office E-Mail[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact Name[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact Phone[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact E-Mail[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]AXCB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]3074[/TD]
[TD="align: center"][/TD]

[TD="align: center"](532) 326-2181[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Client 2[/TD]
[TD="align: center"]BEDF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"][/TD]

[TD="align: center"](811) 112-1616 [/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Client 3[/TD]
[TD="align: center"]HJKLF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]IL[/TD]
[TD="align: center"]3462[/TD]
[TD="align: center"][/TD]

[TD="align: center"](520) 117-4549[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Client 4[/TD]
[TD="align: center"]LDJGJ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]2001[/TD]
[TD="align: center"][/TD]

[TD="align: center"](530) 514-3344[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Client 5[/TD]
[TD="align: center"]JGOPD[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]2117[/TD]
[TD="align: center"][/TD]

[TD="align: center"](310) 224-7642[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Client 6[/TD]
[TD="align: center"]EUFJKG[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]3089[/TD]
[TD="align: center"][/TD]

[TD="align: center"](267) 266-8947[/TD]

</tbody>

Client_List​

 

[My Aswer Is This]

Biggy. I can see your image in post #1 not sure why others cannot see it.

 

[Steve_R]

I try to keep things simple. Vlookup (a single function) is easier for most people to follow than the nested Index & Match functions and will return the same result (subject to swtiches used). However, Index & match is faster on large spreadsheets. It ids easy to convert to Index & Match if that is the formula you prefer.

I am slighly uncertain about the existence of the third sheet (Overdue_Invoices) but read it as a sample representing the output required. Is this sheet something that exists in your workbook that you already generate or a sheet you want generated/populated and repopulated?
For others following, it looks as follows:



Excel 2010

	A	B	C	D	E	F	G	H	I
(811) 112-1616	josh-ganan@bedf.com
(532) 326-2181	aj.gilbert@axcb.com
(530) 514-3344	aarond@ldjgj.com
(532) 326-2181	aj.gilbert@axcb.com

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Invoice #[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Client #[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Invoice Sum[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Buy Date[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Due date[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Days Due[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact Name[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact Phone[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Contact E-Mail[/TD]

[TD="align: center"]2[/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"]23[/TD]
[TD="align: center"]Josh Ganan[/TD]

[TD="align: center"]3[/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"]17[/TD]
[TD="align: center"]AJ Gilbert[/TD]

[TD="align: center"]4[/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"]14[/TD]
[TD="align: center"]Aaron Duner[/TD]

[TD="align: center"]5[/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"]12[/TD]
[TD="align: center"]AJ Gilbert[/TD]

</tbody>

Overdue_Invoices​

 

[Biggy]

Thank You for all Your huge help Steve!

Yes, database is large, so it's more easier to make separate sheet for it, instead of filtering it every time as well as makes easier for other persons to check them out, because it's ready-to-go list, instead of filtering, copying, etc, so in given situation index-match and separate sheet is preferred set-up.

Truth is, index-match who returns all values for given range is powerful tool in lot of situations and this example would help me a lot to understand it and adapt that to other situations. I know how to use simple index-match for one value (lookup alternative), but I don't have any idea how to do it to return all the values within a range and show blank rows in whom isn't information. So this questions is multi-purpose --> to use it for issue on hand, as well as in the future.

Your help is really much appreciated and real saver.

Sincerely,
Tony