Pages: [1]   Go Down
Print
Author Topic: I hate Excel  (Read 4203 times)
sonicyouth

Offline Offline

Posts: 22352





Ignore
« 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 Online

Posts: 11550


Stirlingshire Reds




Ignore
« 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 Offline

Posts: 8265



WWW
« 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 Offline

Posts: 4004


We fucking love you Gumbo!




Ignore
« 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 Offline

Posts: 22352





Ignore
« 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 Offline

Posts: 4004


We fucking love you Gumbo!




Ignore
« 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 Offline

Posts: 27141





Ignore
« 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 Offline

Posts: 22352





Ignore
« 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 Smiley

 Love
Logged
Bewster

Offline Offline

Posts: 4004


We fucking love you Gumbo!




Ignore
« 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 Offline

Posts: 2323





Ignore
« Reply #10 on: Wednesday, March 21, 2012, 03:30:55 »

Put it into Access.

This ^^^
 
Logged
jayohaitchenn
Wielder of the BANHAMMER

Offline Offline

Posts: 12663




« Reply #11 on: Wednesday, March 21, 2012, 10:19:11 »

Fuck excel, do your data extract properly.

http://forums.techarena.in/active-directory/1063268.htm
Logged
Pages: [1]   Go Up
Print
Jump to: