|
Title: I hate Excel Post by: sonicyouth on Tuesday, March 20, 2012, 21:18:08 I have a spreadsheet with two worksheets inside them that have been extracted from AD.
One contains a list of usernames and display names. The other contains usernames and account expiry dates. Annoyingly there's a massive discrepancy between the two due to the extraction tool used being a bit shitty. I need to end up with a spreadsheet containing three columns - display name, username and expiry date. As the username is a unique identifier, I assume I can use this as a reference point between the two worksheets but I cannot for the life of me figure out where to start... I need a formula that will find the username in the second worksheet and return the expiry date, if that makes sense? Dear people of the TEF, I need you (so I can listen to the game in peace) Title: Re: I hate Excel Post by: Ardiles on Tuesday, March 20, 2012, 21:23:32 That's a VLOOKUP formula you need, no? That's how I'd tackle it, anyway.
Title: Re: I hate Excel Post by: Dazzza on Tuesday, March 20, 2012, 21:27:41 A vlookup sounds like what you need.
=vlookup(username worksheet one, all columns from worksheet 2, column containing the expiry dates, false) The expiry date column needs to be to the right of the username column on worksheet 2 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Title: Re: I hate Excel Post by: Bewster on Tuesday, March 20, 2012, 21:36:25 Yeah - agree with above - vlookup is the way to go
Remember that the column you need to lookup up in needs to be left column of the data you are looking up on. Any probs pm me and I'll drop you my email or I am sure the guys above can help Title: Re: I hate Excel Post by: sonicyouth on Tuesday, March 20, 2012, 21:42:09 Does this look right?
=VLOOKUP(Main!A2:A1322, Expiry!A2:B2702, Expiry!B2:B2702, FALSE) The first worksheet (Main) has username, display name and expiry (to be populated...hopefully) as the columns. The second worksheet (Expiry) has expiry date (in dd/mm/yy format) and username as the columns. Cheers for the responses Title: Re: I hate Excel Post by: Bewster on Tuesday, March 20, 2012, 21:51:02 No, it should be
VLOOKUP(Main!A2, Expiry!A2:B2702, 2, FALSE) The 2 represents the 2nd column along. Order the second sheet (Expiry) in this column order - username (a), expiry date (b) The formula will lookup in the first column, find what is wants and count two across (from column a to b) and return the value I can do it for you if you are struggling - send me some sample data Title: Re: I hate Excel Post by: Samdy Gray on Tuesday, March 20, 2012, 21:53:02 =VLOOKUP(Main!A2:A1322, Expiry!A2:B2702, Expiry!B2:B2702, FALSE)
This bit should be a number, not a range. If the username is in column A and the expiry date is in column B then the number should be 1 i.e. find the value in Main!A2 in Expiry!A and return the value in the column 1 column to the right. Title: Re: I hate Excel Post by: sonicyouth on Tuesday, March 20, 2012, 22:03:33 No, it should be I owe you a pint :)VLOOKUP(Main!A2, Expiry!A2:B2702, 2, FALSE) The 2 represents the 2nd column along. Order the second sheet (Expiry) in this column order - username (a), expiry date (b) The formula will lookup in the first column, find what is wants and count two across (from column a to b) and return the value I can do it for you if you are struggling - send me some sample data :love: Title: Re: I hate Excel Post by: Bewster on Tuesday, March 20, 2012, 22:04:28 Did it work ??
you might want to anchor the data reference to ensure you get it all so VLOOKUP(Main!A2, Expiry!$A$2:$B$2702, 2, FALSE) Title: Re: I hate Excel Post by: chalkies_shorts on Tuesday, March 20, 2012, 23:08:11 Put it into Access.
Title: Re: I hate Excel Post by: @MacPhlea on Wednesday, March 21, 2012, 03:30:55 Title: Re: I hate Excel Post by: jayohaitchenn on Wednesday, March 21, 2012, 10:19:11 Fuck excel, do your data extract properly.
http://forums.techarena.in/active-directory/1063268.htm |