Numerating the cells by 2 criteria

H

Hadorian

New Member
Joined
May 4, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
HellO!

As for my data i need to create operation number for each group od data and combined with criteria of another column.
First criteria is Column B
Second criteria is column U within the same group (column B)
And the result is Order number for each group - result is in column Q
There have been some manual entry to start with but i would like to automate it if possible:

4 - AGCT_Maintenance Task Lists Upload Template_ZEAM036 v1.3.xlsx
ABFGHIJPQRSTUV
1Item NumberGROUP NO.KEY DATEGROUP COUNTERGROUP TEXTPLANNING PLANTMAINTENANCE STRATEGYOPERATION NO.WORK CENTERPLANTCONTROL KEYOPERATION DESCRIPTION (SPANISH)OPERATION DESCRIPTION (ENGLISH)
2Max. No. of Characters38DATE44041049444041
3DefinitionNumber of task list to be createdCode of the general task list. Must start with an alphabetDate on which the task list is edited or displayed. All objects (for example, header or operation) that are valid on this key date are displayed. This key and the task list group uniquely identify a task list.This text is transferred to the first line of the long text for the task list.Key uniquely identifying a plant. Key identifying a maintenance strategy or cycle set. Determines in which order the operations of a sequence are carried out.Key identifying the work center.Key uniquely identifying a plant.Determines which business transactions should be executed for the object that belongs to the task list or order (for example scheduling or costing).Describes the operation or sub-operation.Describes the operation or sub-operation.
4RequiredOptional (For General Task List only)RequiredRequiredRequiredRequiredRequired (If Cycle Short Text has value)RequiredRequiredRequiredRequiredRequired ONLY 1 column
5
61MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE ELEKTRIČNOG SKLOPA
71MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE ELEKTRIČNOG SKLOPA
81MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE ELEKTRO-MOTORA
91MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE ELEKTRO-MOTORA
101MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0030MES-MECH3100PM01PROVIJERA IZOLACIJE
111MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0030MES-MECH3100PM01PROVIJERA IZOLACIJE
121MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0040MES-MECH3100PM01KONTROLA I IZMJENA BATERIJE
131MFLKELER1.1.2011.1FL KALMAR ELECTRIC 1K31003100RH0040MES-MECH3100PM01KONTROLA I IZMJENA BATERIJE
142MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE HIDRAULIČKOG SKLOPA
152MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE HIDRAULIČKOG SKLOPA
162MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UREĐAJA ZA DIZANJE
172MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UREĐAJA ZA DIZANJE
182MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0030MES-MECH3100PM01PROVIJERA VILJUŠKARA
192MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0030MES-MECH3100PM01PROVIJERA VILJUŠKARA
202MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0040MES-MECH3100PM01ODRŽAVANJE LEŽAJEVA PORTALA
212MFLKLIFR1.1.2011.1FL KALMAR LIFTING SYSTEM 1K31003100RH0040MES-MECH3100PM01ODRŽAVANJE LEŽAJEVA PORTALA
223MFLKSTRR1.1.2011.1FL KALMAR MAIN STRUCTURE 1K31003100RH0010MES-MECH3100PM01PRANJE I PODMAZIVANJE STROJA
233MFLKSTRR1.1.2011.1FL KALMAR MAIN STRUCTURE 1K31003100RH0010MES-MECH3100PM01PRANJE I PODMAZIVANJE STROJA
243MFLKSTRR1.1.2011.1FL KALMAR MAIN STRUCTURE 1K31003100RH0020MES-MECH3100PM01SERVIS VILIČARA
253MFLKSTRR1.1.2011.1FL KALMAR MAIN STRUCTURE 1K31003100RH0020MES-MECH3100PM01SERVIS VILIČARA
264MFLKOTHR1.1.2011.1FL KALMAR OTHER 1K31003100RH0010MES-MECH3100PM01SIGURNOSNA PROVIJERA
274MFLKOTHR1.1.2011.1FL KALMAR OTHER 1K31003100RH0010MES-MECH3100PM01SIGURNOSNA PROVIJERA
285MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE POGONSKE OSOVINE
295MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE POGONSKE OSOVINE
305MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UPRAVLJAČKE OSOVINE
315MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UPRAVLJAČKE OSOVINE
325MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0030MES-MECH3100PM01ODRŽAVANJE MOTORA
335MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0030MES-MECH3100PM01ODRŽAVANJE MOTORA
345MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0040MES-MECH3100PM01KONTROLA I ČIŠĆENJE DPF FILTERA
355MFLKPOTR1.1.2011.1FL KALMAR POWERTRAIN 1K31003100RH0040MES-MECH3100PM01KONTROLA I ČIŠĆENJE DPF FILTERA
366MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE TOČKOVA GUMA
376MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE TOČKOVA GUMA
386MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0020MES-MECH3100PM01IZMJENA STRAŽNJIH GUMA
396MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0020MES-MECH3100PM01IZMJENA STRAŽNJIH GUMA
406MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0030MES-MECH3100PM01IZMJENA PREDNJIH GUMA
416MFLKTIRR1.1.2011.1FL KALMAR TIRE 1K31003100RH0030MES-MECH3100PM01IZMJENA PREDNJIH GUMA
427MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE ELEKTRIČNOG SKLOPA
437MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE ELEKTRIČNOG SKLOPA
447MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE ELEKTRO-MOTORA
457MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE ELEKTRO-MOTORA
467MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0030MES-MECH3100PM01PROVIJERA IZOLACIJE
477MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0030MES-MECH3100PM01PROVIJERA IZOLACIJE
487MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0040MES-MECH3100PM01KONTROLA I IZMJENA BATERIJE
497MFLLELER1.1.2011.1FL LINDE ELECTRIC 1K31003100RH0040MES-MECH3100PM01KONTROLA I IZMJENA BATERIJE
508MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE HIDRAULIČKOG SKLOPA
518MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE HIDRAULIČKOG SKLOPA
528MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UREĐAJA ZA DIZANJE
538MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UREĐAJA ZA DIZANJE
548MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0030MES-MECH3100PM01PROVIJERA VILJUŠKARA
558MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0030MES-MECH3100PM01PROVIJERA VILJUŠKARA
568MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0040MES-MECH3100PM01ODRŽAVANJE LEŽAJEVA PORTALA
578MFLLLIFR1.1.2011.1FL LINDE LIFTING SYSTEM 1K31003100RH0040MES-MECH3100PM01ODRŽAVANJE LEŽAJEVA PORTALA
589MFLLSTRR1.1.2011.1FL LINDE MAIN STRUCTURE 1K31003100RH0010MES-MECH3100PM01PRANJE I PODMAZIVANJE STROJA
599MFLLSTRR1.1.2011.1FL LINDE MAIN STRUCTURE 1K31003100RH0010MES-MECH3100PM01PRANJE I PODMAZIVANJE STROJA
609MFLLSTRR1.1.2011.1FL LINDE MAIN STRUCTURE 1K31003100RH0020MES-MECH3100PM01SERVIS VILIČARA
619MFLLSTRR1.1.2011.1FL LINDE MAIN STRUCTURE 1K31003100RH0020MES-MECH3100PM01SERVIS VILIČARA
6210MFLLOTHR1.1.2011.1FL LINDE OTHER 1K31003100RH0010MES-MECH3100PM01SIGURNOSNA PROVIJERA
6310MFLLOTHR1.1.2011.1FL LINDE OTHER 1K31003100RH0010MES-MECH3100PM01SIGURNOSNA PROVIJERA
6411MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE POGONSKE OSOVINE
6511MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0010MES-MECH3100PM01ODRŽAVANJE POGONSKE OSOVINE
6611MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UPRAVLJAČKE OSOVINE
6711MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0020MES-MECH3100PM01ODRŽAVANJE UPRAVLJAČKE OSOVINE
6811MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0030MES-MECH3100PM01ODRŽAVANJE MOTORA
6911MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0030MES-MECH3100PM01ODRŽAVANJE MOTORA
7011MFLLPOTR1.1.2011.1FL LINDE POWERTRAIN 1K31003100RH0040MES-MECH3100PM01KONTROLA I ČIŠĆENJE DPF FILTERA
Sheet1
Cell Formulas
RangeFormula
B6:B70B6=IF(F6=F5,B5,B5+1)
H6:H70H6=VLOOKUP(I6,Sheet12!A:D,4,FALSE)
Named Ranges
NameRefers ToCells
Sheet12!_FilterDatabase=Sheet12!$A$4:$D$480H6:H70
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi, you could try this in Q6 and copied down.

Excel Formula: 
=TEXT(IF(B6=B5,IF(U6=U5,P5,P5+10),10),"0000")
 
Upvote 0
Solution
FormR said:
Hi, you could try this in Q6 and copied down.

Excel Formula: 
=TEXT(IF(B6=B5,IF(U6=U5,P5,P5+10),10),"0000")
Click to expand...
It worked like a charm, only instead of column P in the formula should stand Q
 
Upvote 0
Hadorian said:
only instead of column P in the formula should stand Q
Click to expand...

Ooops, yes - I put the formula in column P to test against your manually created results in column Q and forgot to change it afterwards! Well spotted (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
127
Fluff
Fluff
I
  • Solved
Formula to match criteria
2
Replies
18
Views
851
ItalianPlatinum
I
T
  • Solved
Combination of cells 2 rows based on criteria (to build a product accessory link list)
Replies
3
Views
327
DanteAmor
DanteAmor
N
  • Question Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
E
  • Question Question
Formula to populate cells that only meet certain criteria from two tables on another sheet
Replies
1
Views
2K
ebj5883
E

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top