Extract data from table from single date search

[Visy123]

Hi All
Just a newbie searching for help. I have a table that is populated via a userform and would like to enter a date on a different sheet and it would return 3 columns of values associated with that date. The date would appear numerous times in the table. As per below. I would like to extract Client Name, Ad Height, Ad Column, Issue Date (search date), and section. I cant find a fuction or combination that seems to work , at least with my limited knowledge. Any advice would be greatly appreciated

Client Name Contact Name Contact Phone Ad Size Height Ad Size Column Cost per Issue Issue Date Rep Name Section ABC Carpet Cleaning John 63620545 10 7 125.00 29/07/2018 Ray EGN Hair Shop Mary 63629878 10 2 55.00 29/07/2018 Marg EGN Mechanics Plus Mario 63624512 12 3 66.00 9/01/2018 marg Motors Bos Restaurant Phebe 418524458 15 7 225.00 2/01/2018 Ray Eats mazda Cars John 415125785 20 3 450.00 9/01/2018 Ray Motor <colgroup><col><col span="2"><col><col><col><col><col span="2"></colgroup><tbody> </tbody> <colgroup><col><col span="2"><col><col><col><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col span="2"><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

1. You mention that the date could appear numerous times. Do you want the data from those numerous rows all returned on the other sheet? If not all, which one(s) should be returned?

2. Your description says you want to return 3 columns of values, but then you list 5 column headings. Please clarify.

Edit: One further question
3. For this result are you looking for a macro or formula solution?

 

[vcoolio]

Hello Visy123,

Ditto Peter's item (2). However, try the following code placed in a standard module and assigned to a button:-

Sub FindData()

         Dim Srch As String: Srch = Sheet2.[A1].Value
         
Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
        .AutoFilter 7, Srch
        Union(.Columns("A"), .Columns("D:E"), .Columns("G"), .Columns("I")).Offset(1).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Sheet2.[A1].Value = "SEARCH"

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code assumes:

- Sheet1 data starts in row2 with headings in row1.
- Cell A1 in sheet2 is the search cell in which a date is entered.

I hope that this helps,

Cheerio,
vcoolio.

 

[Visy123]

Welcome to the MrExcel board!

1. You mention that the date could appear numerous times. Do you want the data from those numerous rows all returned on the other sheet? If not all, which one(s) should be returned?

2. Your description says you want to return 3 columns of values, but then you list 5 column headings. Please clarify.

Edit: One further question
3. For this result are you looking for a macro or formula solution?

Thank you both

Sorry I wasnt very clear. If I can explain a little better This is a booking sheet for a magazine where clients book an advertisement on a published day, so for this magazine it comes out every Thursday If you have 50 clients they can book for any given issue. For the magazine to do the layout ie where ads will fall in the Mag. a dummy layout is produced with basic information being "Client Name", "Ad Size Height", "Ad Size Column" and "Section" aalong with the search date being the "issue date" If I could use a formula to extract this information as Im even worse with VBA

Thank You Again and Hope I havent waffled on to much

 

[Peter_SSs]

See if this is any help.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Client Name	Contact Name	Contact Phone	Ad Size Height	Ad Size Column	Cost per Issue	Issue Date	Rep Name	Section
2	ABC Carpet Cleaning	John	63620545	10	7	125	29/07/2018	Ray	EGN
3	Hair Shop	Mary	63629878	10	2	55	29/07/2018	Marg	EGN
4	Mechanics Plus	Mario	63624512	12	3	66	9/01/2018	marg	Motors
5	Bos Restaurant	Phebe	418524458	15	7	225	2/01/2018	Ray	Eats
6	mazda Cars	John	415125785	20	3	450	9/01/2018	Ray	Motor
7
Sheet1

In Sheet2,
- Insert a new row 1 and a new column A
- Formula in C1 is copied across
- Formula in A3 is copied down as far as you might ever need.
- Enter the date of interest in B3
- Formula in C3 is copied across and down the same distance as column A
- Row 1 and column A can now be hidden if you want
- Change the date of interest in B3 and the other data should update to reflect the new value.

Excel Workbook
	A	B	C	D	E	F
1			1	4	5	9
2	Index	Issue Date	Client Name	Ad Size Height	Ad Size Column	Section
3	3	9/01/2018	Mechanics Plus	12	3	Motors
4	5		mazda Cars	20	3	Motor
5
Sheet2

 

[Visy123]

Thank You Peter absolutely fantastic I really appreciate your help Thank You

 

[Peter_SSs]

Thank You Peter absolutely fantastic I really appreciate your help Thank You

You are very welcome.