Search excel and data store without wasting operations

[ea1]

I would like to search through an excel column (lets day D) for cells that contain “Y”. Then using that row (2), take another cell from another column (lets say E) with the same row (2) that contains a key.
Then using this key, I put it through a data store search and filter by the key to find the data stored, then delete that data store.

I can figure out how to do it, but cannot figure out how to not have to iterate through every row of the excel sheet and waste all my operations and file transfer.

this is all i have atm:

[Msquare_Automation]

Hi @ea1 ,

Welcome to Make Community!

Can you please share the output bundle from the array aggregator?

How to Share Input/Output bundles

To obtain the input and output bundles for each module:

1. Run the scenario or retrieve it from the History tab.
2. For each module, click the white speech bubble located at the top-right corner.
3. Select “Download input/output bundles” from the dropdown menu.

This will allow you to download the necessary data for further analysis and sharing.

Method 1: Attach a Text File

1. Copy the output data.
2. Paste the data into a text file.
3. Attach the text file to your ticket.

Method 2: Direct Sharing

1. Use the direct sharing option available in the reply section. Please refer to the image below for guidance.

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support | Book Live Implementation
Visit us here
Youtube Channel

[ea1]

[Msquare_Automation]

Hey @ea1

To share the output bundle, you can set the limit in your list rows module and can see the structure of data.

And if you may use get and map function if need to fetch some specific data.

https://www.make.com/en/help/functions/array-functions

Or you can share a loom of your requirement to better assist you.

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support | Book Live Implementation
Visit us here
Youtube Channel

[ea1]

The entire array is way too long to share, but i shared part of the first one for now. The last column would be the Data store key which is not shown here

[
{
“array”: [
{
“id”: 83,
“row”: {
“a”: {
“formula”: 45362.0465277778,
“formulaLocal”: 45362.0465277778,
“numberFormat”: “[$-en-CA]d/mmm/yy;@”,
“text”: “11/Mar/24”,
“value”: 45362.0465277778,
“valueType”: “Double”
},
“b”: {
“formula”: “=IF(X83=0, "",IF(Z83>X83, "Long", "Short"))”,
“formulaLocal”: “=IF(X83=0, "",IF(Z83>X83, "Long", "Short"))”,
“numberFormat”: “General”,
“text”: “Long”,
“value”: “Long”,
“valueType”: “String”
},
“c”: {
“formula”: “”,
“formulaLocal”: “”,
“numberFormat”: “General”,
“text”: “”,
“value”: “”,
“valueType”: “Empty”
},
“d”: {
“formula”: “SOL”,
“formulaLocal”: “SOL”,
“numberFormat”: “General”,
“text”: “SOL”,
“value”: “SOL”,
“valueType”: “String”
},
“e”: {
“formula”: “2H - POWER 4.1”,
“formulaLocal”: “2H - POWER 4.1”,
“numberFormat”: “General”,
“text”: “2H - POWER 4.1”,
“value”: “2H - POWER 4.1”,
“valueType”: “String”
},
“f”: {
“formula”: “=IF(AND(H83="0",N83<>0),F82, $F$2+F82)”,
“formulaLocal”: “=IF(AND(H83="0",N83<>0),F82, $F$2+F82)”,
“numberFormat”: “0.00%”,
“text”: “-1.74%”,
“value”: -0.0174034955543642,
“valueType”: “Double”
},
“g”: {
“formula”: “=G82+I83”,
“formulaLocal”: “=G82+I83”,
“numberFormat”: “0.0%”,
“text”: “14.5%”,
“value”: 0.144871378810624,
“valueType”: “Double”
},
“h”: {
“formula”: 194.097002193,
“formulaLocal”: 194.097002193,
“numberFormat”: “0.00”,
“text”: “194.10”,
“value”: 194.097002193,
“valueType”: “Double”
},
“i”: {
“formula”: “=IF(L83="Y","0",IF(K83="","0",IF(V83="","0",AO83/AM82)))”,
“formulaLocal”: “=IF(L83="Y","0",IF(K83="","0",IF(V83="","0",AO83/AM82)))”,
“numberFormat”: “0.0%”,
“text”: “1.1%”,
“value”: 0.0108129848230995,
“valueType”: “Double”
},
“j”: {
“formula”: “=IF(L83="Y","0",IF(K83="","0",IF(U83="","0",5*(H83/S83))))”,
“formulaLocal”: “=IF(L83="Y","0",IF(K83="","0",IF(U83="","0",5*(H83/S83))))”,
“numberFormat”: “0.0%”,
“text”: “224.4%”,
“value”: 2.24385803301243,
“valueType”: “Double”
},
“k”: {
“formula”: 3,
“formulaLocal”: 3,
“numberFormat”: “General”,
“text”: “3”,
“value”: 3,
“valueType”: “Double”
},
“l”: {
“formula”: “”,
“formulaLocal”: “”,
“numberFormat”: “General”,
“text”: “”,
“value”: “”,
“valueType”: “Empty”
},
“m”: {
“formula”: 1,
“formulaLocal”: 1,
“numberFormat”: “General”,
“text”: “1”,
“value”: 1,
“valueType”: “Double”
},
“n”: {
“formula”: “Y”,
“formulaLocal”: “Y”,
“numberFormat”: “General”,
“text”: “Y”,
“value”: “Y”,
“valueType”: “String”
},

[Msquare_Automation]

@ea1 This json is corrupted. You need to download the output bundle and then share here as described below

How to Share Input/Output bundles

To obtain the input and output bundles for each module:

1. Run the scenario or retrieve it from the History tab.
2. For each module, click the white speech bubble located at the top-right corner.
3. Select “Download input/output bundles” from the dropdown menu.

This will allow you to download the necessary data for further analysis and sharing.

Method 1: Attach a Text File

1. Copy the output data.
2. Paste the data into a text file.
3. Attach the text file to your ticket.

Method 2: Direct Sharing

1. Use the direct sharing option available in the reply section. Please refer to the image below for guidance.

Although, it seems you can use get and map function on array to work this out.

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support | Book Live Implementation
Visit us here
Youtube Channel

[ea1]

this is all im getting when i press output bundles:
[.txt (32.0 KB)

[Msquare_Automation]

@ea1 I can’t see any row number identification in your data. To understand the data structure, I need to see how the data looks like in your sheet. You can share with demo data. As for now, you can refer to below image to use get and map function on your data if this is somehow you want to achieve.

What I did is:

Step 1: Converted the collection into an array

Step 2: getting the key where the text is “Y”

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support | Book Live Implementation
Visit us here
Youtube Channel

[ea1]

This is all i am getting:

[
{
“x”: “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,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an,ao,ap,aq,ar,as,at,au,av,aw,ax,ay,az,ba,bb,bc,bd,be,bf,bg,bh,bi,bj,bk,bl,bm,bn,bo,bp,bq,br,bs,bt,bu,bv,bw,bx,by,bz,ca,cb,cc,cd,ceY”
}
]