Finding Multiple Pattern values

[jumiprado]

Hey Guys, I have this information

Id	Name	Object	Sub Object	Date	Location	Stage	State	Person In charge
1115/02	AAAA1	Ob01	SubOb01	01/02/2002	CSS 1	S1	St1	Jhon
23785/02	BBBB2	Ob01	SubOb02	03/05/2002	CSS 2	S1	St2	Marie
456/03	CCC1	Ob02	SubOb01	15/01/2003	CSS 1	S2	St2	Paul
3523/04	AAAA1	Ob02	SubOb01	04/10/2004	CSS 3	S1	St1	Paul
5879/05	AAAA1	Ob01	SubOb01	20/09/2005	CSS 4	S2	St2	Marie Jane

And i need to have something like this

First of all this info:

Name	Object	Sub Object	Quantity
AAAA1	Ob01	SubOb01	2
AAAA1	Ob02	SubOb01	1
BBBB2	Ob01	SubOb02	1
CCC1	Ob02	SubOb01	1

And other table with

Name	Object	Sub Object	Information
AAAA1	Ob01	SubOb01	Already Exists
AAAA1	Ob02	SubOb01	Not Exists
BBBB2	Ob01	SubOb02	Not Exists
CCC1	Ob02	SubOb01	Not Exists

Thanks!

[dplr-rn]

See script below for for the first table. with regards to the second table i dont understand the intent/logic behind it. but on face value you can just add a new column to table 2 like below

TestData:
LOAD
    Id,
    Name,
    Object,
    "Sub Object",
    "Date",
    Location,
    Stage,
    State,
    "Person In charge"
FROM [lib://Downloads/Test.xlsx]
(ooxml, embedded labels, table is Sheet1);
Data:
LOAD
    Name,
    Object,
    "Sub Object",
     Count(Id) as Quantity,
 if(Count(Id)>1,'Already Exists','Not Exists') as Information
resident TestData Group By Name,
    Object,
    "Sub Object";
    
drop table TestData;