Calculate date in a specific product list

%3CLINGO-SUB%20id%3D%22lingo-sub-2798053%22%20slang%3D%22en-US%22%3ECalculate%20date%20in%20a%20specific%20product%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798053%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20need%20to%20be%20able%20to%20calculate%20a%20shortage%20date%2Ftime.%20Attached%20document%20represents%20all%20assembly%20product%20starts%20in%20a%20factory.%20Based%20on%20what%20the%20user%2Fvlookup%20will%20enter%20in%20G1-G4%2C%20I%20would%20like%20to%20return%20the%20product%20number%20in%20this%20list%20and%20the%20start%20position%20of%20the%20day%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20example%2C%20the%20user%20enter%20a%20shortage%3A%26nbsp%3B%3CSPAN%3E111112%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20with%207%2B5%20positions%20ahead%20I%20would%20like%20this%20script%20to%20return%3A%3C%2FP%3E%3CP%3EDate%3A%206%2F10%3C%2FP%3E%3CP%3EProduct%3A%20111116%3C%2FP%3E%3CP%3E1%3Ast%20product%20start%20of%20the%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(And%20the%20layout%20of%20the%20excel%20product%20list%20can%20unfortunately%20not%20be%20changed%2Fredesigned).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2798053%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798880%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20date%20in%20a%20specific%20product%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F998213%22%20target%3D%22_blank%22%3E%40RobinJakobsson123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20describe%20in%20more%20detail%20what%20your%20plans%20are.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_self%22%3E%3CSPAN%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798920%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20date%20in%20a%20specific%20product%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798920%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3BHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EG2%20%3D%20111112%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20product%20I%20want%20to%20start%20the%20calculation%20from.%20Then%20calculate%207%2B5%20(G3%20%2B%20G4%20number%20of%20steps)%20down%20the%20list%20and%20return%20the%20new%20product%20number%2C%20date%20and%20number%20of%20productstart%20of%20that%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EG%20columns%20are%20where%20the%20user%20will%20enter%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I would need to be able to calculate a shortage date/time. Attached document represents all assembly product starts in a factory. Based on what the user/vlookup will enter in G1-G4, I would like to return the product number in this list and the start position of the day .

 

As an example, the user enter a shortage: 111112

 

So with 7+5 positions ahead I would like this script to return:

Date: 6/10

Product: 111116

1:st product start of the day.

 

I guess that one solution could be a VBA that counts down the column only where format is "XXXXXX".

 

(And the layout of the excel product list can unfortunately not be changed/redesigned).

7 Replies

@RobinJakobsson123 

Please describe in more detail what your plans are.

Welcome to your Excel discussion space!

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE Hi,

 

G2 = 111112

 

This is the product I want to start the calculation from. Then calculate 7+5 (G3 + G4 number of steps) down the list and return the new product number, date and number of productstart of that day.

 

G columns are where the user will enter the data.

@RobinJakobsson123 

For that layout

image.png

it could be

Product:

=INDEX(FILTER(C:C, C:C <> ""),XMATCH($G$2, FILTER(C:C, C:C <> "") )+$G$3+$G$4)

Date:

=INDEX(A:A, XMATCH(0, C1:INDEX(C:C, XMATCH(  G7,C:C))+0,0,-1) )

 

@Sergei Baklan 

 

Wonderful, that works. Just had to change all the ',' to ';'

 

The list is in another excel sheet. How do I change the path from C:C to Sheet1!C:C ?

 

 

@RobinJakobsson123 

Exactly as you said

=INDEX(
   FILTER( Sheet1!C:C, Sheet1!C:C <> ""),
   XMATCH( $G$2,
           FILTER(Sheet1!C:C, Sheet1!C:C <> "")
   ) + $G$3+$G$4
)

and

=INDEX(
  Sheet1!A:A,
  XMATCH( 0,
          Sheet1!C1:INDEX( Sheet1!C:C, XMATCH(  G7, Sheet1!C:C))+0,
          0,
          -1)
 )

Please check in second sheet attached.

 

The only I'd do not use entire columns in formulas - better named ranges, dynamic ranges or simple range with some gap for future expansion.

@Sergei Baklan Thank you very much, very helpful.

 

How do I calculate the number of start that day? So if the product number is the 3rd start in the list 2021-10-02, I want the function to return only '3'.

 

And I discovered another issue. I have made dropdown menus in my table. When I expand (adding a row) the dropdown doesn't follow, also the borders I've made. How do I change that?

 

And this changes when I am expanding the table, in a way that I do not want it to change.

=INDEX(Startprogram!A:A; XMATCH(0; Startprogram!C5:INDEX(Startprogram!C:C; XMATCH( [@[Product no]];Startprogram!C:C))+0;0;-1) )

 

BR

Thank you for your help. Do you have any solution for:

How do I calculate the number of productstart that day? So if the product number is the 3rd start in the list 2021-10-02, I want the function to return only '3'.