VLOOKUP Problem (duplicating lines)

[mykal66]

Hey folks - I've spent hours trying to figure out how to fix the issue i am having with a VLOOKUP and just hitting a brick wall

I have adapted a calendar i found elsewhere for use a forward planner for work. My manager then asked if i could filter the events by either department or event type which i thought would be easy using VLOOKUP.

Users enter details on the Schedule Tab and then a VLOOKUP is meant to copy anything for a particular event to a corresponding TAB e.g. all external meetings for any department would be copied to the external meetings tab. This works as long as there is only 1 type of event on the schedule but if there are several types of events (attached workbook) entries are duplicated.

The attached workbook demonstrates the problem i am having. In the schedule for August 2014 there are 6 entries (2 external meetings and 4 reminders) so what should happen is that the External Meeting Tab should only have 2 entries but it has 6 with the bottom one being duplicated up to the point of the first external meeting. The Reminder Tab also does the same in duplicating an actual entry so each tab shows 6 entries inc duplicates.

In the end a macro will just show / hide whichever tab the user wants to see which i have got working - I will carry on working on it today but any advice / help would be really appreciated.

Thank you as always

Mykal12106

[westconn1]

i can certainly see why you get duplicate rows
looks like a possible case for sumproduct, read all the examples in the sumproduct sub forum, there may be some solution for you

[mykal66]

i can certainly see why you get duplicate rows
looks like a possible case for sumproduct, read all the examples in the sumproduct sub forum, there may be some solution for you

Hi westconn1

Thanks for looking at this for me and your suggestion. I've looked at sumproduct but can't see how this would work as I'n not looking to total anything, just copy the relevant lines / range to another worksheet.

Thanks anyway

Mykal

[westconn1]

i could easily insert the filtered rows using VBA, but i struggle to do via formula
i will look some more

[holycow]

Hi

I couldn't view your attachment because my post count is 0.

As westconn1 said it's easy to do with VBA but if you want to use formula "Excel Is Fun" has a great formula approach on youtube

I cant post the link also because of post count 0.

Have a look on youtube "Excel Is Fun Magic Trick 900"

[mykal66]

Hi

I couldn't view your attachment because my post count is 0.

As westconn1 said it's easy to do with VBA but if you want to use formula "Excel Is Fun" has a great formula approach on youtube

I cant post the link also because of post count 0.

Have a look on youtube "Excel Is Fun Magic Trick 900"

I am happy to use VBA if it would be easier though not sure where to start with that, if either of you could help that would be brilliant.

holycow - as you cant see the attachment this is essentially what i need it to do:

If any cell in column M has the value MEETING then the range J-N in the same row would be copied into the next available row in a worksheet called Meetings

Thanks to both

[holycow]

Without having viewed you attachment but based on your description, please see attached.

[mykal66]

Hi holycow. I have looked at the workbook your uploaded and managed to use your method in my existing workbook. Your method is far better than the long winded way I was trying to do things.

Thank you so much for your help and for teaching me something new - it is very much appreciated

Mykal

[holycow]

I didn't like the helper in the Summary sheet Column A. Please see attached where I have used rows() as an incrementer instead.

I just adapted the solution from Excel is Fun ;)

[mykal66]

Cheers - I will have a look at this too

Mykal

[holycow]

I had a thought, if you make the helper column in schedule sheet in column A, you can use vlookup instead of index/match