Extract data from table from single date search

V

Visy123

New Member
Joined
Jul 13, 2018
Messages
11
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:confused:

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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?
 
Last edited:
Upvote 0
Hello Visy123,

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

Code: 
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.
 
Upvote 0
Peter_SSs said:
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?
Click to expand...


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
 
Upvote 0
See if this is any help.

Excel Workbook
ABCDEFGHI
1Client NameContact NameContact PhoneAd Size HeightAd Size ColumnCost per IssueIssue DateRep NameSection
2ABC Carpet CleaningJohn6362054510712529/07/2018RayEGN
3Hair ShopMary636298781025529/07/2018MargEGN
4Mechanics PlusMario63624512123669/01/2018margMotors
5Bos RestaurantPhebe4185244581572252/01/2018RayEats
6mazda CarsJohn4151257852034509/01/2018RayMotor
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
ABCDEF
11459
2IndexIssue DateClient NameAd Size HeightAd Size ColumnSection
339/01/2018Mechanics Plus123Motors
45mazda Cars203Motor
5
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
R
  • Question Question
Dependant dropdown in a table searching from between 2 date values
Replies
3
Views
824
Marcelo Branco
M
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
786
Viau1337
V
R
  • Question Question
Lookup/Filter?
Replies
3
Views
877
Fluff
Fluff
D
  • Question Question
Pop Up Requesting Date
Replies
0
Views
586
dave5770
D

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top