I hate Excel

[sonicyouth]

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)

[Ardiles]

That's a VLOOKUP formula you need, no?  That's how I'd tackle it, anyway.

[Dazzza]

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)

[Bewster]

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

[sonicyouth]

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

[Bewster]

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

[Samdy Gray]

=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.

[sonicyouth]

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

 

[Bewster]

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)

[chalkies_shorts]

Put it into Access.

[@MacPhlea]

Put it into Access.

This ^^^
 

[jayohaitchenn]

Fuck excel, do your data extract properly.

http://forums.techarena.in/active-directory/1063268.htm