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:


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

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Biggy,


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


Regards,
DILIPandey
 

Biggy

New Member
Joined
Feb 14, 2014
Messages
22
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

Active Member
Joined
Oct 28, 2015
Messages
350
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
ABCDEFGHIJK
1Invoice #Client #Invoice SumBuy DateDue datePaidDays DueContact NameContact PhoneContact EmailCompany email
2AB10001Client 1$5172/01/201616/01/2016Y0
3AB10002Client 2$92123/01/20166/02/2016N24Josh Ganan(811) 112-1616 josh-ganan@bedf.cominfo@bedf.com
4AB10003Client 3$121127/01/201610/02/2016Y0
5AB10004Client 1$135129/01/201612/02/2016N18AJ Gilbert(532) 326-2181aj.gilbert@axcb.comoffice@axcb.com
6AB10005Client 4$13211/02/201615/02/2016N15Aaron Duner(530) 514-3344aarond@ldjgj.comhelp@ldjgj.com
7AB10006Client 1$9553/02/201617/02/2016N13AJ 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/2016N4
11AB10010Client 3$100020/02/20165/03/2016N-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
CellFormula
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

Active Member
Joined
Oct 28, 2015
Messages
350
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

New Member
Joined
Feb 14, 2014
Messages
22
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

Active Member
Joined
Oct 28, 2015
Messages
350
Forgot to post the lookup table for those who could not view the attachments

Excel 2010
ABCDEFGHIJK
1Client #Company NameAddressCityStateZip CodeOffice PhoneOffice E-MailContact NameContact PhoneContact E-Mail
2Client 1AXCBAZ3074office@axcb.comAJ Gilbert(532) 326-2181aj.gilbert@axcb.com
3Client 2BEDFNY2130info@bedf.comJosh Ganan(811) 112-1616 josh-ganan@bedf.com
4Client 3HJKLFIL3462help@hjklf.comSimon Jarvish(520) 117-4549sjarvish@hjklf.com
5Client 4LDJGJTX2001help@ldjgj.comAaron Duner(530) 514-3344aarond@ldjgj.com
6Client 5JGOPDNY2117info@jgopd.comJudith Larson(310) 224-7642judith.larson@jgopd.com
7Client 6EUFJKGAZ3089help@eufjkg.comJeff Raz(267) 266-8947jraz@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
 

Steve_R

Active Member
Joined
Oct 28, 2015
Messages
350
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
ABCDEFGHI
1Invoice #Client #Invoice SumBuy DateDue dateDays DueContact NameContact PhoneContact E-Mail
2AB10002Client 2$92123/01/20166/02/201623Josh Ganan(811) 112-1616 josh-ganan@bedf.com
3AB10004Client 1$135129/01/201612/02/201617AJ Gilbert(532) 326-2181aj.gilbert@axcb.com
4AB10005Client 4$13211/02/201615/02/201614Aaron Duner(530) 514-3344aarond@ldjgj.com
5AB10006Client 1$9553/02/201617/02/201612AJ Gilbert(532) 326-2181aj.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

New Member
Joined
Feb 14, 2014
Messages
22
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
 

Forum statistics

Threads
1,082,104
Messages
5,363,164
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top