sonicyouth
Offline
Posts: 22352
|
|
« 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)
|
|
|
Logged
|
|
|
|
Ardiles
Online
Posts: 11550
Stirlingshire Reds
|
|
« Reply #1 on: Tuesday, March 20, 2012, 21:23:32 » |
|
That's a VLOOKUP formula you need, no? That's how I'd tackle it, anyway.
|
|
|
Logged
|
|
|
|
Dazzza
Offline
Posts: 8265
|
|
« Reply #2 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)
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #3 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
|
|
|
Logged
|
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #4 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
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #5 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
|
|
« Last Edit: Tuesday, March 20, 2012, 21:53:29 by Bewster »
|
Logged
|
|
|
|
Samdy Gray
Dirty sneaky traitor weasel
Offline
Posts: 27141
|
|
« Reply #6 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.
|
|
|
Logged
|
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #7 on: Tuesday, March 20, 2012, 22:03:33 » |
|
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
I owe you a pint
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #8 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)
|
|
|
Logged
|
|
|
|
chalkies_shorts
|
|
« Reply #9 on: Tuesday, March 20, 2012, 23:08:11 » |
|
Put it into Access.
|
|
|
Logged
|
|
|
|
@MacPhlea
Offline
Posts: 2323
|
|
« Reply #10 on: Wednesday, March 21, 2012, 03:30:55 » |
|
Put it into Access.
This ^^^
|
|
|
Logged
|
|
|
|
jayohaitchenn
Wielder of the BANHAMMER
Offline
Posts: 12663
|
|
« Reply #11 on: Wednesday, March 21, 2012, 10:19:11 » |
|
|
|
|
Logged
|
|
|
|
|