Vlookup to find multiple entries

[Danz_s]

Hi there,

Hoping someone can shed some light on these requests.

I have a worksheet with data starting on row 5, the data is in columns A through to T. In column B are dates of when the data is populated for each row, multiple entries can be populated each day therefore the date is repeated. When the data is entered it continues underneath the previous days entries.

Challenge 1 :

On the blank second sheet of the workbook I am wanting to enter a date into cell A1 (eg todays date) then starting in cell A3 I want to look up the date that is in A1 on the first sheet (called Report) then return the infomation that is in columns A,G,S & T from all the enries that match.
I have tried VlookUp but ran into the follwing problems, first the look up array is in column B (I can change this if need be to be in column A on the 'Report' page) the next problem is that the VlookUp would only return the first entry it found. The amount of entries can vary everyday.
Hopefully someone has some suggestions on this.

Challenge 2 : (sorry, I know im asking a lot)

Same workbook, I want to be able to look up the value that is in column F (starting at row 5 down to the last entry) compare this to another open workbook that is populated with data in columns A through to AD, the lookup array in the second workbook is in column B, if it finds a match i need to return the value from column J from the second work book and copy the result into column U on the 'Report' sheet for the relevant row.
I have a user form in the first work book already that I can use to be able to select the second workbook that it needs to look into, this user form is currently used to get the data into the original workbook on the 'Report' page.

My apologies if that comes across as a bit jumbled i wasnt too sure the best way of explaining.

Any help on either of these is very much appreciated

 

[Aladin Akyurek]

Try to provide a small sample...

 

[Danz_s]

hi, hope this works

New Product List mrexcel sample.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4	Month	Date	Group	Cat	Cls	New Stockcode	Long Description	Replacing Stockcode	Retail Range	Promotional	Seasonal	Planogram	Planogram	Height mm	Width mm	Depth mm	Merch Type	Display Type	Submitted By	Approved By
5	Jul	18/07/07	Computers	XH	036	xh1444	accessory	no	yes	Yes		NOT PLANOGRAMMED	PROMO ONLY	267	315	98	Shelf	Boxed	joe	john
6	Jul	18/07/07	Computers	XH	036	xh1444	tba		yes	No	Yes	NOT PLANOGRAMMED	NOT PLANOGRAMMED	267	315	98	Shelf	Boxed	jim	paul
Report

This is the first work book withe sheet named as 'report' i have only included the first 2 lines of data, hope this is enough

 

[Danz_s]

this one is sample data from the second workbook for the second part of my original question

Copy of Test Extract.xls
	A	B	C	D	E	F	G	H	I	J
1	APN	ITM	ITMDSC	VNDNME	ITMBRD	GRPDSC	CAT	CLS	CLSDSC	STS
2	123456	XH1401	unknown	unknown	unknown	unknown	XH	087	Accessories	Future
3	123456	XH1444	unknown	unknown	unknown	unknown	XH	036	Accessories	Future
4	123456	XH1704	unknown	unknown	unknown	unknown	XH	037	Accessories
5	123456	XH1709	unknown	unknown	unknown	unknown	XH	037	Accessories
6	123456	XH1710	unknown	unknown	unknown	unknown	XP	054	Accessories	Quit
7	123456	XH1712	unknown	unknown	unknown	unknown	XH	050	Accessories
DSE

thanks again

 

[Danz_s]

Update :

I believe I have solved the first part, I had completely overlooked Pivot tables, this seems to do the job a treat.

Still stuck on the second part

Thanks in advance

 

[Danz_s]

I have come up with this formula

=INDEX('[Copy of Test Extract.xls]DSE'!$A$2:$Y$65536,MATCH(F5,'[Copy of Test Extract.xls]DSE'!$B$2:$B$65536,TRUE),10)

This will return me the right value, can anyone suggest a way to write this into vba code so it will populate all the cells in column U where there is data for that row. The name of the sheet [Copy of Test Extract.xls]DSE will change from time to time however the data ranges and format wont. As before i have a userform that i could use to get the WBname so will need to incorporate this into the code as well

cheers