Automatic organization

[Pietro Di Micio]

Hi Folks,

I am wondering if its possible to create an automatic organization of some data based on a key code, image and details below.

Tks in advance.

 

[Peter_SSs]

Thanks for the extra XL2BB data.
I am assuming that each day you paste the new data into cell B7, not further down column B. Post back with details if that is not the case.
I am also assuming that there is none of the green/yellow colour in columns B or E before this code is first activated.

Test with a copy of your workbook.

This is a Worksheet_Change event code and will be triggered when the data is pasted into B7. (It may also be triggered at other times but it shouldn't do anything on those occasions.
To implement the code
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by pasting some data into the B7 area.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lrB As Long, lrE As Long

  If Not Intersect(Target, Range("B7")) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    lrB = Range("B" & Rows.Count).End(xlUp).Row
    lrE = Range("E" & Rows.Count).End(xlUp).Row
    Range("B7:E" & IIf(lrB > lrE, lrB, lrE)).FormatConditions.Delete
    Range("E7:F" & lrE).Insert Shift:=xlToRight
    With Range("E7:E" & lrE)
      .Formula2 = Replace("=FILTER(G$7:H$#,G$7:G$#=B7,INDEX(FILTER(G$7:H$#,ISNA(MATCH(G$7:G$#,B$7:B$#,0))*ISNA(MATCH(G$7:G$#,E$6:E6,0)),""""),1,0))", "#", lrE)
      .Resize(, 2).Value = .Resize(, 2).Value
      .Offset(, 2).Resize(, 2).Delete Shift:=xlToLeft
      .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E7<>"""",B7=E7)"
      .FormatConditions(1).Interior.Color = 13693658
      .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E7<>"""",B7<>E7)"
      .FormatConditions(2).Interior.Color = 65535
    End With
    With Range("B7:B" & lrB)
      .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(B7<>"""",B7=E7)"
      .FormatConditions(1).Interior.Color = 13693658
      .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(B7<>"""",B7<>E7)"
      .FormatConditions(2).Interior.Color = 65535
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub

 

[Pietro Di Micio]

Thanks a lot, but allow me to share som details about the trouble that I am facing; looking to the sample below:

1) From column B to column O I updated every day doing a copy paste from others reports;
2) Column P to Z I keep some data that I want to keep the tracking (special the column R that I write steps/status);
3) Note that Column G and Column P are the KEY CODE, in other words the key to match the data;
4) What I want; due the fact that I have a lot of lines (=500), its ver complicated to match the informations, so when I copy & paste the data as I explained in the item 1 I need that the columns that I explained in the item 2 match the same line in a automatic way.

Thanks in advance.

pdm_cockpit.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
31
32															1/31/24
33															2/29/24
34															3/15/24
35															3/22/24
36		A - Purchase	B - Negotiate	C - Select			-				-				3/29/24
37		FLSM	IAE	OPP. OWNER	COMPANY	OPP. DESCRIPT.	OPP. ID	FCST CATEGORY	OPP PHASE	CLOSE DATE	NET SAP USD	LOB	PARTNER	PBM	CQ LINEARITY	OPP. ID2	COMPANY2	CADENCE	SQ/GAF	WHY CHANGE	WHY NOW	WHY SAP	DECISION MAKER	PROPOSAL	COLOR CODE	TABLE
38															M1				ok	y	y	y	y	y	|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100%	100%
39															M1				-	-	-	-	-	-	0%	0%
40															M1				-	-	-	-	-	-	0%	0%
41															M1				-	-	-	-	-	-	0%	0%
42															M1				-	-	-	-	-	-	0%	0%
COCKPIT - CQ

Cell Formulas
Range	Formula
G36	G36	=SUBTOTAL(3,G38:G1055)
K36	K36	=SUBTOTAL(9,K38:K1055)
Y38:Y42	Y38	=REPT("|",(COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5)*75)&(COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5)*100&"%"
Z38:Z42	Z38	=COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5
O38:O42	O38	=IF(A38<=$O$32,"M1",IF(A38<=$O$33,"M2",IF(A38<=$O$34,"SC",IF(A38<=$O$35,"LW",IF(A38>=$O$36,"QE","QE")))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T38:X537	Cell Value	="-"	text	NO
T38:X537	Cell Value	="n"	text	NO
T38:X537	Cell Value	="y"	text	NO
S38:S537	Cell Value	= "-"	text	NO
S38:S537	Cell Value	="help needed"	text	NO
S38:S537	Cell Value	="pending"	text	NO
S38:S537	Cell Value	="ok"	text	NO
S40:S537	Cell	contains an error	text	NO
P38:P537	Expression	=IF(P38<>0;ISERROR(MATCH(P38;$G$30:$G$1000;0));0)	text	NO
O38:O537,R38:R537	Cell	contains an error	text	NO
H38:H537	Expression	=(OR(H38="Committed";H38="Probable";H38="Booked/Won"))	text	NO
K38:K537	Cell Value	>=$K$30	text	NO
K38:K537	Cell	contains an error	text	NO
Y38:Y537	Expression	=$Z38=0%	text	NO
Y38:Y537	Expression	=AND($Z38<=49%;$Z38>=1%)	text	NO
Y38:Y537	Expression	=AND($Z38>=50%;$Z38<80%)	text	NO
Y38:Y537	Expression	=AND($Z38>=80%;$Z38<=100%)	text	NO
G30,G38:G537	Expression	=IF(G30<>0;ISERROR(MATCH(G30;$P$30:$P$1000;0));0)	text	NO
L31:N35	Cell	contains an error	text	NO
I30,I38:I1055	Expression	=IF(AND(L30<>0;L30<>"T&E");ISERROR(MATCH(I30;$B$36:$E$36;0));0)	text	NO
J30,J38:J537	Expression	=IF(AND(J30<>0;L30<>0;L30<>"T&E";H30<>0;H30<>"Booked/Won");ISERROR(MATCH(J30;$K$35:$O$35;0));0)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
S38:S537	List	=$S$26:$S$29
T38:X537	List	=$T$27:$T$29

 

[Peter_SSs]

Is it complex?

Yes, especially when the layout, data and explanation keep changing!

The way that I would attempt this (using a Windows OS) is not available on a Mac & I don't have a Mac to test alternatives so I'm not sure I can help.