VLOOKUP with Drop Down Selection, unsorted data


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average. Display Modes
  #1  
Unread 15th February 2012, 19:41
kaj's Avatar
kaj kaj is offline
Junior Member
MS Office Version: Excel 2003
MS Office Skill Level: Can use VBA
 
Join Date: Aug 2011
Location: Devizes, England
Posts: 8
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (4) kaj is on a distinguished road
England
Question VLOOKUP with Drop Down Selection, unsorted data
     Excel: Functions   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: Functions   

Hi All
I have data in a history sheet by year and week no, approx 225 entries per week. In a another sheet (Week Data) the user can select a week no and year from drop downs and the sheet is populated which in turns updates charts. Works fine if I select the first set of data, which happens to be at the top of the history, but when I select another week and/or year I get duplicated data.
Can not be a pivot table or use code on this occassion.

This is my formula in Week Data B8 can anyone help?

My Formula:
=IF($A8>$A$7,"",VLOOKUP($A8,'History Data'!$A8:$X5080,COLUMNS('Weekly Data'!$B8:C8),0))


There is probably a simple answer, but this workbook has pickled my brain, I did not create it and have already had to clean up.

Thanks
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.


Last edited by Simon Lloyd; 16th February 2012 at 01:03. Reason: Added formula tags
The Code Cage Advertisment
Advertisement
  #2  
Unread 15th February 2012, 20:40
Pete_UK's Avatar
Pete_UK Pete_UK is offline
Solution Centre Professional
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Mar 2009
Location: Warrington, England
Age: 62
Posts: 625
Posts Per Day: 0.30
My Top Tip Count: 0
Thanks to others: 8
Thanked 132 Times in 131 Posts
Reputation: 2587 (37) Pete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond repute
Great_Britain
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Column A in both sheets does not seem to do anything, so I shall use this - in one sheet this will set up a sequential count of the records for each unique week number, and in the other one I shall have a match formula which will be used by INDEX formulae in the other columns.

However, I'm just about to go out now, so I'll get back to you with this in a few hours time (unless someone else posts a solution in the meantime).

Hope this helps.

Pete

Reply & Quote Reply


1 members found this post helpful.
  #3  
Unread 15th February 2012, 21:02
kaj's Avatar
kaj kaj is offline
Junior Member
MS Office Version: Excel 2003
MS Office Skill Level: Can use VBA
 
Join Date: Aug 2011
Location: Devizes, England
Posts: 8
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (4) kaj is on a distinguished road
England
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Hi Pete

Think I got lost somewhere trying to find a solution!

As I was using two drop downs (year and week number) I used column A in History for the following:
My Formula:
=AND(B13='Weekly Data'!$K$1)*(C13='Weekly Data'!$I$1)

Year=Year in drop down & Week Number=Week Number in drop down, if both did it put a 1 in column A if not a zero. Then in Weekly Data cell A7 is the MAX of column A in History Data.

Then in column A Weekly Data I added 1's all the way down, hence the starting of the formula:

My Formula:
=IF($A8>$A$7,"",VLOOKUP($A8,'History Data'!$A8:$X5080,COLUMNS('Weekly Data'!$B8:C8),0))


So I was trying to look for the 1's. If I keep sorting the data in the History it works, but I have internal and external users of the workbook so that didn't help, users are filtering and sorting all the time in the history. The purpose of the Weekly Data is a user can select the year & week and 5 other sheets are updated (charts and summaries).

Thanks I will await your reply, with finger crossed.

K


Last edited by Simon Lloyd; 16th February 2012 at 01:03.
The Code Cage Advertisment
Advertisement
  #4  
Unread 16th February 2012, 00:45
Pete_UK's Avatar
Pete_UK Pete_UK is offline
Solution Centre Professional
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Mar 2009
Location: Warrington, England
Age: 62
Posts: 625
Posts Per Day: 0.30
My Top Tip Count: 0
Thanks to others: 8
Thanked 132 Times in 131 Posts
Reputation: 2587 (37) Pete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond repute
Great_Britain
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by kaj View Post

... I used column A in History for the following:
=AND(B13='Weekly Data'!$K$1)*(C13='Weekly Data'!$I$1)

...

Then in column A Weekly Data I added 1's all the way down,

...

So I was trying to look for the 1's ...

Hi Kaj,

yes, I could see what you were trying to do in those columns, and I could also see that it wouldn't work. What I was trying to get across to you is that I can make use of those columns in my solution, so that the overall structure of your data would not change.

Anyway, I'm back now, so I'll grab a coffee and then put it all together for you and submit it later on.

Pete

Reply & Quote Reply


1 members found this post helpful.
  #5  
Unread 16th February 2012, 01:14
kaj's Avatar
kaj kaj is offline
Junior Member
MS Office Version: Excel 2003
MS Office Skill Level: Can use VBA
 
Join Date: Aug 2011
Location: Devizes, England
Posts: 8
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (4) kaj is on a distinguished road
England
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Thanks Pete, your a Star as always.

Whats annoying is I did this in minutes with VB and it works a treat in a second.

K

  #6  
Unread 16th February 2012, 01:28
Pete_UK's Avatar
Pete_UK Pete_UK is offline
Solution Centre Professional
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Mar 2009
Location: Warrington, England
Age: 62
Posts: 625
Posts Per Day: 0.30
My Top Tip Count: 0
Thanks to others: 8
Thanked 132 Times in 131 Posts
Reputation: 2587 (37) Pete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond repute
Great_Britain
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


I didn't know if you would still be around. I noticed just now that you are using XL2003, so I'll have to make a slight adjustment as I won't be able to use COUNTIFS. I'll have to insert a new column A in the history sheet.

Pete

Reply & Quote Reply


1 members found this post helpful.
The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #7  
Unread 16th February 2012, 02:05
Pete_UK's Avatar
Pete_UK Pete_UK is offline
Solution Centre Professional
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Mar 2009
Location: Warrington, England
Age: 62
Posts: 625
Posts Per Day: 0.30
My Top Tip Count: 0
Thanks to others: 8
Thanked 132 Times in 131 Posts
Reputation: 2587 (37) Pete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond repute
Great_Britain
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Right then, there are basically four formulae in the attached workbook to achieve what you were after. As i just mentioned, I had to insert a new column A in the history sheet and I've put this formula in A8:

My Formula:
=C8&D8


and this one in B8:

My Formula:
=IF(A8="","-",A8&"_"&COUNTIF(A$7:A8,A8))


and these are copied down at least to the bottom of your data, but will not cause errors if they are copied too far - I've copied to row 5090 to show this (you get hyphens to show where they have been copied to). It should be fairly obvious what they do - set up a sequential number for unique year/week number combination, thus giving a unique ID for each record.

It seems to me though that you insert new data at the top of your existing data in the history sheet, presumably by inserting blank rows, and if this is the case then you will need to ensure that these formulae are copied upwards to cover the new records. That's why I've started the COUNTIF range from A7.

I've put this formula in A8 of the weekly sheet:

My Formula:
=IF(ISNA(MATCH($K$1&$I$1&"_"&ROWS(A$1:A1),'History Data'!B:B,0)),"-",MATCH($K$1&$I$1&"_"&ROWS(A$1:A1),'History Data'!B:B,0))


This generates the sequential number for the year and week number selected in K1 and I1 and then finds the row on the history sheet where that record occurs. The other formula is this one in B8:

My Formula:
=IF($A8="-","",INDEX('History Data'!C:C,$A8))


which returns the appropriate data from the history sheet. This is copied across to column X, and then I've re-instated the formatting from the column below. Then row 8 can be copied down as far as you like. I could see that the maximum number of entries that you have in any particular week is 275, so I've copied the formulae down to row 307 to give you a bit of leeway.

Anyway, all you need to do is to pick a week or year on the top row, and the data will automatically adjust.

Hope this helps.

Pete
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.


Last edited by Pete_UK; 16th February 2012 at 10:33.
Reply & Quote Reply


1 members found this post helpful.
  #8  
Unread 16th February 2012, 02:09
kaj's Avatar
kaj kaj is offline
Junior Member
MS Office Version: Excel 2003
MS Office Skill Level: Can use VBA
 
Join Date: Aug 2011
Location: Devizes, England
Posts: 8
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (4) kaj is on a distinguished road
England
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Hi Pete

Yes still around, documenting all the clean ups I've done from the original file, why x formula/x column(s) have been deleted/amended. The original file received was 4591kb before I started adding the additional data etc required, cleaned up formulas first the file reduced to 2882kb, then started adding the additions. Wish this was my file to start.

K

  #9  
Unread 16th February 2012, 02:18
Pete_UK's Avatar
Pete_UK Pete_UK is offline
Solution Centre Professional
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Mar 2009
Location: Warrington, England
Age: 62
Posts: 625
Posts Per Day: 0.30
My Top Tip Count: 0
Thanks to others: 8
Thanked 132 Times in 131 Posts
Reputation: 2587 (37) Pete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond reputePete_UK has a reputation beyond repute
Great_Britain
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


I wish you luck. My attachment is reported on the Forum as 4.47mB, but that is a 97/03 compatible file saved from XL2007, so if you save it using XL2003 the file size should drop considerably.

Time for another coffee now, I think.

Pete

Reply & Quote Reply


1 members found this post helpful.
The Code Cage Advertisment
Advertisement
  #10  
Unread 16th February 2012, 02:47
kaj's Avatar
kaj kaj is offline
Junior Member
MS Office Version: Excel 2003
MS Office Skill Level: Can use VBA
 
Join Date: Aug 2011
Location: Devizes, England
Posts: 8
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 1
Thanked 0 Times in 0 Posts
Reputation: 10 (4) kaj is on a distinguished road
England
Default Re: VLOOKUP with Drop Down Selection, unsorted data

------ Register to get rid of these "In Post" ads! ------


Hi Pete

Excellent it worked, but I then added week 6 2012 into the history (inserted at top) and the weekly data seletion doesn't pick it up, what am I doning wrong?


Attached file

K
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.


Last edited by kaj; 16th February 2012 at 03:00.
The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
data, drop, selection, unsorted, vlookup

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 20:26.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University