Listing Missed Details in New Table

[senthilvelan]

Hi Masters,
I need the solution for following situation in the Table-01 contains all the details; Table-02 has missing sum data
Now in the Table-03, I want the list of missing details given date Range in 'E16 + G16.

Thanks In Advance.

	Table-01					Table-02					Table-03
Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value
01-Apr	aab	123	150		01-Apr	aab	123	150		01-Apr	abd	345	250
01-Apr	abd	345	250		01-Apr	acb	654	150		02-Apr	abd	124	1505
01-Apr	acb	654	150		02-Apr	acb	515	50		03-Apr	axx	554	540
02-Apr	abd	124	1505		02-Apr	axv	215	154		03-Apr	afv	514	122
02-Apr	acb	515	50		03-Apr	asd	976	2130
02-Apr	axv	215	154		04-Apr	acv	334	554
03-Apr	axx	554	540
03-Apr	asd	976	2130
03-Apr	afv	514	122
04-Apr	acv	334	554
					Date
				01-Apr	to	04-Apr

<tbody>
</tbody>

 

[oldbrewer]

Table-01							Table-02							Table-03
Date	C. Name	Inv No	Value	concat1	missing		Date	C. Name	Inv No	Value	concat2			Date	C. Name	Inv No	Value
01-Apr	aab	123	150	aab12315043191			43191	aab	123	150	aab12315043191			43191	abd	345	250
01-Apr	abd	345	250	abd34525043191	M		43191	acb	654	150	acb65415043191			43192	abd	124	1505
01-Apr	acb	654	150	acb65415043191			43192	acb	515	50	acb5155043192			43193	axx	554	540
02-Apr	abd	124	1505	abd124150543192	M		43192	axv	215	154	axv21515443192			43193	afv	514	122
02-Apr	acb	515	50	acb5155043192			43193	asd	976	2130	asd976213043193
02-Apr	axv	215	154	axv21515443192			43194	acv	334	554	acv33455443194
03-Apr	axx	554	540	axx55454043193	M
03-Apr	asd	976	2130	asd976213043193
03-Apr	afv	514	122	afv51412243193	M
04-Apr	acv	334	554	acv33455443194
		your 4 missing rows are highlighted with an "M"
		the formula in the cell below "missing" is
		=IF(ISERROR(OFFSET($L$3,MATCH(E4,$L$4:$L$20,0),1)),"M","")
		the formula for the first concat1 "value" in E4 is
		=B4&C4&D4&A4

<colgroup><col><col><col><col><col><col><col><col span="4"><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Since your requirement seems to be dependant on dates falling between the E16 and G16 dates, it would have been a good idea to include some rows where the dates did not already fall in that date range.

In any case, if I have understood correctly, you could do it ...

A) Directly, with the formula shown below in K3 copied across and down (you may need to reformat columns M & N to an appropriate number format after copying the formulas across)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Table-01					Table-02					Table-03
2	Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value
3	1-Apr	aab	123	150		1-Apr	aab	123	150		1-Apr	abd	345	250
4	1-Apr	abd	345	250		1-Apr	acb	654	150		2-Apr	abd	124	1505
5	1-Apr	acb	654	150		2-Apr	acb	515	50		3-Apr	axx	554	540
6	2-Apr	abd	124	1505		2-Apr	axv	215	154		3-Apr	afv	514	122
7	2-Apr	acb	515	50		3-Apr	asd	976	2130
8	2-Apr	axv	215	154		4-Apr	acv	334	554
9	3-Apr	axx	554	540
10	3-Apr	asd	976	2130
11	3-Apr	afv	514	122
12	4-Apr	acv	334	554
13
14
15						Date
16					1-Apr	to	4-Apr
Missing Data 1

B) Using a helper column (for me formula in E3 copied down - leaving E2 empty) and then the simpler formula in K3 copied across and down (again, reformatting M & N may be required)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Table-01					Table-02					Table-03
2	Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value		Date	C. Name	Inv No	Value
3	1-Apr	aab	123	150	0	1-Apr	aab	123	150		1-Apr	abd	345	250
4	1-Apr	abd	345	250	1	1-Apr	acb	654	150		2-Apr	abd	124	1505
5	1-Apr	acb	654	150	1	2-Apr	acb	515	50		3-Apr	axx	554	540
6	2-Apr	abd	124	1505	2	2-Apr	axv	215	154		3-Apr	afv	514	122
7	2-Apr	acb	515	50	2	3-Apr	asd	976	2130
8	2-Apr	axv	215	154	2	4-Apr	acv	334	554
9	3-Apr	axx	554	540	3
10	3-Apr	asd	976	2130	3
11	3-Apr	afv	514	122	4
12	4-Apr	acv	334	554	4
13
14
15						Date
16					1-Apr	to	4-Apr
Missing Data 2

 

[senthilvelan]

Thank you for the great help. I'll check this formula & Reply soon.