Posts by mljohn

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?

Quote from bosco_yip

In B2, formula copied down :

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),99)

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

Quote from Carim

Hello,

You can modify your formula

Code

=INDEX(Sheet2!$A$1:$IM$886,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0))

Hope this will help

Display More

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?

Quote from Carim

Hello,

In cell B2, you can test following

Code

=INDEX(Sheet2!$A$1:$M$37,MATCH(Sheet1!$A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0))

Hope this will help

Display More

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

Test 2 criteria.xlsx

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?

AMPC Test.xlsx

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?

Thanks

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

https://www.mrexcel.com/forum/…ank-date.html#post5276763

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.