I have a lookup formula that finds the last entry and returns the date in that column in the header.
IFERROR(LOOKUP(2,1/(B2:IM2<>""),$B$1:$IM$1),"")
How do I adjust it to find the first entry and give me the first date in that column in the header?
I have a lookup formula that finds the last entry and returns the date in that column in the header.
IFERROR(LOOKUP(2,1/(B2:IM2<>""),$B$1:$IM$1),"")
How do I adjust it to find the first entry and give me the first date in that column in the header?
This worked. Thanks for your expertise
I have a column with street addresses but some of them have a name in front of the address.
How do I remove the non-numeric characters before the first numeric character?
Examples:
Debbie9676 S. Mount Jordan Rd
Ivory Ridge Office3401 N. Center St. #250
June3535 E. Little Cottonwood Ln 84
Display MoreHello,
You can modify your formula
Hope this will help
Could you help me understand the formula a little better so I can expand it down to 886 rows and also many columns to the right?
Display MoreHello,
In cell B2, you can test following
Hope this will help
Works great but I am having a problem expanding it to the right and down. I have 886 rows down and a long way to the right.
Works great.
Formula: like xllookup or Index/Match that uses two criteria.
If Sheet1 A2 is found in range Sheet2 A:A then return the cell in the range of Sheet2 $B:$IE that matches Sheet1 B$1
I have a workbook with 5 sheets for my suppliers. In the M-Column I have their part numbers.
Some suppliers have one part number for the item and sometimes it is 2 part numbers and sometimes it is 5 part number for the item, it varies. It depends on the supplier.
Typically there is a matching part number or numbers amongst the various suppliers. I want to know what those matching common part number is for all of the suppliers.
Can this be done?
I have a formula that gives me a blank cell because the first match for the company name in the date field is blank. IFERROR(INDEX('QB-GenCleanReport'!H:H,INDEX(MATCH("*"&A2&",*",'QB-GenCleanReport'!B:B&",",0),)),"") Col B is the Company name that should match A2 and Column H has the dates. The dates are in descending order however some of the company names which have several entries do not have a date. How do I fix this to give me the most current date that is not blank?
I don't understand what to do with this pivot table. It seems to show me that there are duplicates which I already know. I am only removing the OEM duplicates.
I have a large list of OEM numbers that have duplicates and I cannot get the "Remove Duplicates" in Excel to work. How do I do this? [ATTACH]n1222753[/ATTACH]
Scott T at Mr. Excel came up with the solution.
COUNTIFS($A$1:$HC$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),$A$1:$HC$1,"<="&HG2,A2:HC2,">0") Thanks for your support Matt
Here is the sample. Ultimately I want to know the 5 year average value of my customers and the Lifetime value.
[ATTACH]n1218490[/ATTACH]
I have a sheet with the column headers as dates by month and year. Column A is "Jan 2002", Column B is "Feb 2002" etc until Column HC "May 2019".
In Column HG I have a the date of the last time my customer ordered "Aug 2007" for example.
For each month there is either a dollar amount or it is blank for each customer.
I want to count the number of times each customer ordered (non blank) within a 5 year period counting back from the last date they ordered.
If the last date they ordered was Oct 2006 then I want to count the number of months that the customer ordered between Oct 2001 - Oct 2006.
If within that time period there were 50 months were the customer ordered something then I want the result to show "50" in column HQ.
Thanks for the help
Thanks, Carim, all the dates in column A seem to be FALSE or Text but I don't understand how that helps me with the original question.
The previous one had too many tabs and this one only has the correct spreadsheet. I created a video explanation. https://www.screencast.com/t/OlF4YmjJD6ic
Sorry but my excel sheet that I posted was the wrong one. Here is the correct spreadsheet.
I don't see that the dates are sometimes text and sometimes actual date format.
The dates are always sorted in descending order.
I would prefer an Excel formula because I understand formulas better but a macro would be ok.
Thanks
I have a list of dates of customer purchase activity. I need a formula in column (J) that finds all dates that belong to a particular customer and figure out how many days since their previous order. The customers are separated by two blank rows. Each customer may have duplicate dates but I need to calculate since the last different date. Then I need to calculate the average from the range of those answers, (see the gray in the green columns). I don't know how to calculate if there is a different date, (from blank to blank). I manually did the first and last to show what I am trying to create. See attachment.