Comparing two columns vales from filtered table and original table and decide the values are present or not.

santosh chaturvedi 0

Hello all

Here is one situation:

I have to make a check cell of comparing two columns of filtered table (red & blue columns) with its its mother data table (red & white).

And want to make decision that the two columns value from mother table is included in filtered table or not.

Example :

Mother table > User's image

Filtered table (without zeros): User's image

Formula req: if(VAlues of two columns from Filtered table (without zeros) in included from mother table), "OK","NOTOK")

________----------------------------------------

Filtered table is shown below.

User's image

Mother table is shown below.

User's image

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-05-27T23:35:36.67+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.

Answer recommended by moderator

Chaturvedi, Santosh 430

Hello Greetings Hendrix > Many thanks for clarifications provided.

Moving further ..

I am searching a solution for this case.

I cannot able to attach the excel sheet as your system is not allowing it, : The image is shown below.

..............................................................

See cell i5 > it shows ( [σa ] → 0.25, 0.75,1.8... 50) and ([σm ]↓ =27.5,20.5,19.5....30.5)
I need to Filter the data in column A,B,C with following requirements.

..............................................................

In column A6 - A41 : The values of [σa ] → 0.25 shall come. ( see example is shown)
In column B6 - B41 : The values of I6-I41 shall come.
In column C6 - C41 : The values of J6-J41 shall come that shall not contain zeros. ........................................................

The same will be repeated for [σa ] → 0.75 0.75,1.8... 50,..... which will be sequenced below the table of [σa ] → 0.25, see image below.

In column A42 - A77 : The values of [σa ] → 0.75 shall come. see example is shown
In column B42 - B77 : The values of I6-I41 shall come.
In column C42 - C77 : The values of J6-J41 shall come that shall not contain zeros. ........................................................

The same will be repeated for [σa ] → 1.8...........26 located in row 5

--> (See A78, B78m C78 for message)

.......................................................

I am attaching the excel sheet for more information with markings

User's image

Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-02T22:22:12.96+00:00

Hi @Chaturvedi, Santosh,

I greatly appreciate your sharing.

I will work with the solution on my testing environment and reach out again with the best possible guidance for your concern as soon as possible.

In the meantime, I will recommend this answer to pin it to the top so other users from the forum can find your post and provide you more guidance/suggestions.

Thank you for your patience and understanding.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-03T20:12:40.8533333+00:00
Hi @Chaturvedi, Santosh,

For your concern, you can try using this formula to achieve your request:

=LET( sa,$F$1:$Z$1, sm,$E$2:$E$37, vals,$F$2:$Z$37, r,ROWS(vals), c,COLUMNS(vals), seq,SEQUENCE(r*c), flatVals,TOCOL(vals,0,TRUE), flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)), flatSm,INDEX(sm,MOD(seq-1,r)+1), keep,(flatVals<>"")*(flatVals<>0), HSTACK(FILTER(flatSa,keep),FILTER(flatSm,keep),FILTER(flatVals,keep)))

The ranges in the formula are based on my testing worksheet, so you need to modify the ranges so the formula can work properly in your data structure.

The result of my suggested formula will be like this (it filters up to the last [σa] value):

After that, you can use Conditional Formatting or manually format the table color based on your preferences.

Hope this would be a suitable solution for your concern. If you have any questions or need any further clarification, please feel free to reach out again.

Thank you for your patience and understanding. I'm looking forward to your reply.
Chaturvedi, Santosh 430 Reputation points

2026-06-04T08:56:56.1333333+00:00
Hello & Greetings Hendrix from sailing city Kiel (Germany !!)

Many thanks for solution provided. The formula with proper ranging is very well working.

I have this last two wishes and then the issues will be fixed for this case.

"Since you have formulated the formula then you can easily put these requirements in the formulation."

...............................................................................

SORTING: Max to Min values of Ni. Please see the image below. This will be applicacle to all table entries of 0.25, 0.75. 1.3, 1.8.. as shown in your feed back image.

.....................................

2: Checks: This is for quality checks that the values of Ni for 0.25, 0.75,1.3,1.8....came in table of columns from filter table is SAME or not.

Please see below.

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-04T20:37:33.7866667+00:00
Hi @Chaturvedi, Santosh,

Please clarify me if I have misunderstood anything about your request.

1/ Formula for Sorting

There are different ways to sort based on your preference:

If you want to perform sorting while extract the filter table, you can use this formula to replace the formula given in my previous response:

=LET(sa,$F$1:$Z$1,sm,$E$2:$E$37,vals,$F$2:$Z$37,r,ROWS(vals),seq,SEQUENCE(r*COLUMNS(vals)),flatVals,TOCOL(vals,0,TRUE),flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)),flatSm,INDEX(sm,MOD(seq-1,r)+1),keep,(flatVals<>"")*(flatVals<>0), fSa, FILTER(flatSa,keep), fSm,FILTER(flatSm,keep), fNi, FILTER(flatVals,keep), SORTBY(HSTACK(fSa,fSm,fNi),fSa,1,fNi,-1))

If you want to sort your existing spilled result instead of replacing the extraction formula, use this formula instead

=SORTBY(A2#,INDEX(A2#,0,1),1,INDEX(A2#,0,3),-1)

If you want to have one separate spilled table per [σa] value, you can use this formula with a helper [σa] cell (e.g. AH2). When you input the [σa] value in cell AH2, the formula will return the corresponding sorted table of that input value

=LET(src,$A$2#,k,AH2,grp,FILTER(src,INDEX(src,0,1)=k),SORTBY(grp,INDEX(grp,0,3),-1))

2/ Formula for Checking

In your first check cell, use this formula then copying it right to the last column:

=IF(SUMPRODUCT(($A$2:$A$357=F$1)*($C$2:$C$357))=SUM(F$2:F$37),"OK","NOT OK")

Note: The ranges in the formula are based on my testing worksheet, so you need to modify the ranges so the formula can work properly in your data structure.

If you need any further assistance, please don't hesitate to ask and I will be happy to help as much as possible within my abilities.

Thank you for your patience and understanding. Hope to hear from you soon.
Chaturvedi, Santosh 430 Reputation points

2026-06-05T07:07:03.78+00:00

Hello - Greeting !!

These formula is yielding the solution--

=LET(sa,$F$1:$Z$1,sm,$E$2:$E$37,vals,$F$2:$Z$37,r,ROWS(vals),seq,SEQUENCE(rCOLUMNS(vals)),flatVals,TOCOL(vals,0,TRUE),flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)),flatSm,INDEX(sm,MOD(seq-1,r)+1),keep,(flatVals<>"")(flatVals<>0), fSa, FILTER(flatSa,keep), fSm,FILTER(flatSm,keep), fNi, FILTER(flatVals,keep), SORTBY(HSTACK(fSa,fSm,fNi),fSa,1,fNi,-1))

=IF(SUMPRODUCT(($A$2:$A$357=F$1)*($C$2:$C$357))=SUM(F$2:F$37),"OK","NOT OK")

It means the soluntions is made.

Many thanks for your experties!!

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-05T15:48:28.13+00:00

You're welcome @Chaturvedi, Santosh,

I'm very glad to know that my sharing would help resolve your concern.

For any concerns or questions in the future, please don’t hesitate to reach out. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

I wish you a wonderful day ahead.
Chaturvedi, Santosh 430 Reputation points

2026-06-08T06:32:19.1733333+00:00

Hello Hendrix -- Greetings !!

I want to learn more detail and advance level of formulation in Excel. Do you have or Microsoft have any link and learning course material or interactive training material on the advance excel formation.

I am mechibaical Engineering with master degree and i do have lot interest in these areas.

Please help me or share if you have any tarining material so that I can learn more in depth in this reagrds

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-09T20:14:35.0166667+00:00
Hi @Chaturvedi, Santosh,

For advanced learning based on your background as mechanical engineering, I would recommend focusing on the following Excel formula areas:

Core formulas: XLOOKUP, INDEX, MATCH, and FILTER for advanced lookup and data extraction; IF, IFS, AND, OR, and SWITCH for logical calculations

Modern formulas like LET and LAMBDA for building cleaner

Reusable formulas SORT, UNIQUE, SEQUENCE, and other dynamic array formulas

Engineering and mathematical functions such as CONVERT, SIN, COS, TAN, POWER, SQRT, and statistical functions

These topics will be useful for engineering-related work such as material property lookup, stress calculations, unit conversion, tolerance analysis, and technical reporting. Once you are comfortable with the core formulas, you can move into more advanced analysis tools such as PivotTables, Power Query, Power Pivot.

You can start with these official Microsoft resources and practice step by step using your own engineering examples:

Excel help & learning: this is Microsoft’s official Excel learning hub, covering formulas, functions, PivotTables, data analysis, templates, troubleshooting, and other Excel features.

Excel functions (by category) | Microsoft Support: this page lists Excel functions by category, including Engineering, Math & Trigonometry, Statistical, Logical, Lookup & Reference, Financial, Date & Time, and Text functions. It is very useful for advanced formula learning.

You can also take a look at other online websites/courses (e.g Excel Courses & Certificates [2026] | Coursera) for more practice resources.

(This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.)

Hope this will be helpful for your concern. Please don't hesitate to reach out again if you need anything else in the meantime.

Many best wishes for a great day.

8 additional answers

santosh chaturvedi 0 Reputation points

2026-05-28T12:15:24.3466667+00:00

how to hide columns in table with all zero values
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
IlirU 2,651 Reputation points Volunteer Moderator

2026-05-28T09:06:01.51+00:00
Hi @santosh chaturvedi

See the screenshot above and apply this formula in cell L8.
```
=IF(K8:K43 = 0, "", BYROW(G8:K43, LAMBDA(a, IF(BYCOL(BYROW(A8:E38 = a, AND), OR), "OK", "NOT OK"))))
```
Change the range in formula as per you need.

HTH

IlirU
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-27T21:07:23.32+00:00

Hi @santosh chaturvedi,

I understand this way based on the information you shared, so if I have misunderstood anything about your request, please let me know.

Based on your screenshot, I have recreated two sample mother and filtered data tables like this:

Based on your requirement "if(VAlues of two columns from Filtered table (without zeros) in included from mother table), "OK","NOTOK")", in cell H3, you can use this formula then copy paste it down: =IF(G3=0,"",IF(COUNTIFS($A$3:$A$33,F3,$B$3:$B$33,G3)>0,"OK","NOTOK"))

If this is not your goal, please share a sample of your expected outcome so I can clearly understand your request and provide a more appropriate guidance for your concern.

If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
santosh chaturvedi 0 Reputation points

2026-05-27T16:12:20.2866667+00:00

Is that the vedio of this process is avaailable
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Comparing two columns vales from filtered table and original table and decide the values are present or not.

8 additional answers

Your answer