Forum Discussion
Help with Excel formula
I am trying to do a date mapping in the following data basis the the value present fields... Value > 0 , the first value month will start month and the last value >0 should be the end month.. Sample data i have given... Could you please help
| Identity | 1-Jan-25 | 1-Feb-25 | 1-Mar-25 | 1-Apr-25 | 1-May-25 | 1-Jun-25 | 1-Jul-25 | 1-Aug-25 | 1-Sep-25 | 1-Oct-25 | 1-Nov-25 | 1-Dec-25 | 1-Jan-26 | 1-Feb-26 | Start Period | End period |
| Item1 | 0 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 25-Mar | 25-Aug |
| Item2 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 25-Feb | 25-Jul |
| Item3 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 25-Feb | 25-Jul |
| Item4 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 25-Jun | 25-Nov |
| Item5 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 0 | 25-Jun | 26-Jan |
Hi anu2021
Can you please try the below steps to get your requirement ?
1. Use below formula to get Start Period
=TEXT(INDEX($B$1:$O$1, MATCH(TRUE, INDEX(B2:O2>0, 0), 0)), "yy-mmm")
2. Use below formula to get End Period
=TEXT(INDEX($B$1:$O$1, MATCH(1, INDEX((B2:O2>0)*(COLUMN(B2:O2)=MAX(IF(B2:O2>0, COLUMN(B2:O2)))), 0), 0)), "yy-mmm")If this answers your questions, kindly accept it as a solution and give kudos.
I have also attached the excel file.
6 Replies
i am unable to attach the file.. so tried pasting it in table view instead of Image.. 😞
Hi anu2021
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. The sample data must actually represent your raw data and not simply the desired output. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Also, your image is very hard to read
hi, I am trying to attach the file.. however i am not finding an option to attach.. so updated it with the table view intead of image..
Hi anu2021
Can you please try the below steps to get your requirement ?
1. Use below formula to get Start Period
=TEXT(INDEX($B$1:$O$1, MATCH(TRUE, INDEX(B2:O2>0, 0), 0)), "yy-mmm")
2. Use below formula to get End Period
=TEXT(INDEX($B$1:$O$1, MATCH(1, INDEX((B2:O2>0)*(COLUMN(B2:O2)=MAX(IF(B2:O2>0, COLUMN(B2:O2)))), 0), 0)), "yy-mmm")If this answers your questions, kindly accept it as a solution and give kudos.
I have also attached the excel file.
This helps and solves the problem for 2 lakh+ records... Thank you 🙂
