• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The formula for extracting data from one page to another without repeating the flight name on the same date

Hany ali

Active Member
Dear teachers and dear friends, please kindly help me find an equation to extract all trips during one date without repetition .. This is an example of this with the results required to be found in the attached file
So that the data in the two columns A & B is brought from the Data page to the Excursion page as in the file .. that is, the same date is fetched and repeated on several rows by the number of different flight names that were implemented on the same date and the trip name is not repeated during the same date
 

Attachments

  • Example.xlsm
    60.9 KB · Views: 6
thanks alot ,but every time Pivot table is dificult for my work
it possible to be by formula
because more easy for me and I will bring alot of data after that
 
1] Using "Remove Duplicates" built-in function to create a "Tour Date" unique list in "Data" sheet H1:H12

2] Create a Validation Data dropdown list In "Excursion" sheet B1

3] In "Excursion" sheet B3, formula copied down :
=IFERROR(INDEX(Data!A$2:A$102,AGGREGATE(15,6,ROW($1:$101)/(Data!B$2:B$102=B$1)/(COUNTIF(B$2:B2,Data!A$2:A$102)=0),1)),"")

66231

Regards
Bosco
 

Attachments

  • Tour Name List.xlsx
    58.6 KB · Views: 4
thanks alot ,but if Possible by the same Formula as first Column for Excursion Name and the Second for the Date
 
Excuse me, professor. I think there is a missing link, which is that I don't want to fetch data through the dropdown list of dates, just fetch data without repetition without any condition
Except that the names of the trips are not repeated during the same date, and that the same date is also brought to multiple rows by the number of different flight names during the same date ,as example in my upload file
ExDate
Aspendos Turkish Bath​
16/05/19​
Dolphin Show-Dolphina​
16/05/19​
Full Day Jungle AquaPark​
16/05/19​
Intro Diving​
16/05/19​
Royal Utopia​
16/05/19​
Scuba Trip​
16/05/19​
Submarine​
16/05/19​
 
hello,you can to use this
Code:
=IFERROR(INDEX(Data!$A$2:$A$102,AGGREGATE(15,6,ROW($A$1:$A$102)/(MATCH(Data!$A$2:$A$102&Data!$B$2:$B$102,Data!$A$2:$A$102&Data!$B$2:$B$102,0)=ROW($A$1:$A$102)),ROWS($1:1))),"")
 

Attachments

  • Example2.xlsm
    62.5 KB · Views: 6
Back
Top