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
1	Invoice #	Client #	Invoice Sum	Buy Date	Due date	Paid	Days Due	Contact Name	Contact Phone	Contact Email	Company email
2	AB10001	Client 1	$517	2/01/2016	16/01/2016	Y	0
3	AB10002	Client 2	$921	23/01/2016	6/02/2016	N	24	Josh Ganan	(811) 112-1616	josh-ganan@bedf.com	info@bedf.com
4	AB10003	Client 3	$1211	27/01/2016	10/02/2016	Y	0
5	AB10004	Client 1	$1351	29/01/2016	12/02/2016	N	18	AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com	office@axcb.com
6	AB10005	Client 4	$1321	1/02/2016	15/02/2016	N	15	Aaron Duner	(530) 514-3344	aarond@ldjgj.com	help@ldjgj.com
7	AB10006	Client 1	$955	3/02/2016	17/02/2016	N	13	AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com	office@axcb.com
8	AB10007	Client 1	$365	6/02/2016	20/02/2016	Y	0
9	AB10008	Client 5	$419	9/02/2016	23/02/2016	Y	0
10	AB10009	Client 6	$946	12/02/2016	26/02/2016	N	4
11	AB10010	Client 3	$1000	20/02/2016	5/03/2016	N	-4

<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 Cell Formula H2 =IF(G2>=10,VLOOKUP(B2,Client_List!A:K,9,FALSE),"") I2 =IF(G2>=10,VLOOKUP(B2,Client_List!A:K,10,FALSE),"") J2 =IF(G2>=10,VLOOKUP(B2,Client_List!A:K,11,FALSE),"") K2 =IF(G2>=10,VLOOKUP(B2,Client_List!A:K,8,FALSE),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Client #	Company Name	Address	City	State	Zip Code	Office Phone	Office E-Mail	Contact Name	Contact Phone	Contact E-Mail
2	Client 1	AXCB			AZ	3074		office@axcb.com	AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com
3	Client 2	BEDF			NY	2130		info@bedf.com	Josh Ganan	(811) 112-1616	josh-ganan@bedf.com
4	Client 3	HJKLF			IL	3462		help@hjklf.com	Simon Jarvish	(520) 117-4549	sjarvish@hjklf.com
5	Client 4	LDJGJ			TX	2001		help@ldjgj.com	Aaron Duner	(530) 514-3344	aarond@ldjgj.com
6	Client 5	JGOPD			NY	2117		info@jgopd.com	Judith Larson	(310) 224-7642	judith.larson@jgopd.com
7	Client 6	EUFJKG			AZ	3089		help@eufjkg.com	Jeff Raz	(267) 266-8947	jraz@eufjkg.com

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
1	Invoice #	Client #	Invoice Sum	Buy Date	Due date	Days Due	Contact Name	Contact Phone	Contact E-Mail
2	AB10002	Client 2	$921	23/01/2016	6/02/2016	23	Josh Ganan	(811) 112-1616	josh-ganan@bedf.com
3	AB10004	Client 1	$1351	29/01/2016	12/02/2016	17	AJ Gilbert	(532) 326-2181	aj.gilbert@axcb.com
4	AB10005	Client 4	$1321	1/02/2016	15/02/2016	14	Aaron Duner	(530) 514-3344	aarond@ldjgj.com
5	AB10006	Client 1	$955	3/02/2016	17/02/2016	12	AJ Gilbert	(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>
</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