Comparing 2 sheets and output in the report

Question

I want to compare data of Table 1 to Table 2 and based on the comparision, I want the output .&nbsp;Table 1 is the main table and has all the fields populated.Table 2 will have some fields missing for item sometimes..example- length, breath...etc&nbsp;&nbsp;I want the whole item line from table 2 as an output if any of the fields has missing data.Table 2 can have more lines of items but the output should have only those lines which are in Table 1.&nbsp;&nbsp;Table 1&nbsp;On Purchase Order&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item CodeDescriptionInner QtyOuter QtyInner LengthInner WidthInner HeightInner WeightAvg. Carton WeightOuter WidthOuter HeightOuter length123ABC26413124452012134343DEF634242544512313223144JHK286224764365121223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table 2Total Items&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item CodeDescriptionInner QtyOuter QtyInner LengthInner WidthInner HeightInner WeightAvg. Carton WeightOuter WidthOuter HeightOuter length123ABC26413124452012134343DEF634&nbsp;4&nbsp;54&nbsp;12313223144JHK28622&nbsp;764365&nbsp;1223234gfd476&nbsp;2346246&nbsp;6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Output Table-Only missing data items&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;43DEF634&nbsp;4&nbsp;54&nbsp;12313223144JHK28622&nbsp;764365&nbsp;1223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

learner03 , One of the option you can check is except 
&nbsp;
a new tbale = except(TableB, TableA)

learner03 · Answer

amitchandak&nbsp;Sorry to mention that Number of Columns in both the tables are not same and the "if condition" to check which line to to selected from Table 2 will be based on the following columns only and not other columns-Inner QtyOuter QtyInner LengthInner WidthInner HeightInner WeightAvg. Carton WeightOuter WidthOuter HeightOuter length

ryan_mayu · Answer

learner03&nbsp;
here is a workaround for you.
1. merge table and find out the exactly match records and filter out them.
2. use left ID merge table 2 again to get the data from table 2.
pls see the attachment below

learner03 · Answer

ryan_mayu&nbsp;Can you please show similar working on the dataset attached?&nbsp;https://drive.google.com/file/d/1mlutRXI1oiQ5bg8AlBI0_g7q7vmLk-Rr/view?usp=sharinghttps://drive.google.com/file/d/1mlutRXI1oiQ5bg8AlBI0_g7q7vmLk-Rr/view?usp=sharing&nbsp;

ryan_mayu · Answer

learner03&nbsp;
it shows access denied.

Forum Discussion

Comparing 2 sheets and output in the report

7 Replies

Related content

Google sheets

VAR and SWITCH Output issues

Table Sort , sort order , comparer

DMV Column Output Definitions and Options

DateDiff Incorrect output

Table 1	On Purchase Order

Item Code	Description	Inner Qty	Outer Qty	Inner Length	Inner Width	Inner Height	Inner Weight	Avg. Carton Weight	Outer Width	Outer Height	Outer length
123	ABC	2	64	1	3	1	24	45	20	12	13	43
43	DEF	6	34	2	4	2	54	45	12	31	32	23
144	JHK	2	86	2	2	4	76	43	65	12	12	23


Table 2	Total Items

Item Code	Description	Inner Qty	Outer Qty	Inner Length	Inner Width	Inner Height	Inner Weight	Avg. Carton Weight	Outer Width	Outer Height	Outer length
123	ABC	2	64	1	3	1	24	45	20	12	13	43
43	DEF	6	34		4		54		12	31	32	23
144	JHK	2	86	2	2		76	43	65		12	23
234	gfd	4	76		23	4	6	2	4	6		6

Output Table-Only missing data items
43	DEF	6	34		4		54		12	31	32	23
144	JHK	2	86	2	2		76	43	65		12	23