Compare two columns in two separate spreadsheets, then output info from the column next to it

0
votes

I have a series of spreadsheets with Remote Desktop login information (Username and time of login, separate columns) and another spreadsheet with everyone's username and which department they work in (separate columns again).

I've been asked to gather how many logins per department rather than how many times a particular user has logged on.

So I need something (powershell or maybe excel?) to read column one on the RDP spreadsheet (the username) then compare it to column one on the departments spreadsheet (the username again), if it matches then output to another spreadsheet the department (column two on the department spreadsheet) from which the user is from.

Ive tried much google'ing but have failed dismally, any help, even pointing me at another reasource, would be greatly appreciated.

Thanks in advance,

Chris

EDIT: Sorry for not saying what i'd tried before, nothing has given me any sort of usable output...

So this is what ive tried so far...

Im new to powershell (like three weeks ago, started using it) so be gentle :)

$RDP = Import-Csv 'T:\RDP Logons\RDPLogons.csv'
$DEPT = Import-Csv 'T:\RDP Logons\UsersAndDept.csv'
compare $RDP $DEPT

So this didnt work, next i looked into opening the spreadsheet (with PS) then selecting columns and putting them into variables (the user specified $blah entries?) this quickly got way beyond my poor PS skills, but this is as far as i got...

`# Specify the path to the Excel file and the WorkSheet Name
$FilePath = "T:\RDP Logons\RDPLogons"
$SheetName = "RDPLogons"

`# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application

`# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

`# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)

`# Load the WorkSheet 'RDPLogons'
$WorkSheet = $WorkBook.sheets.item($SheetName)

`# Select range of cells from WorkSheet
$Data = $WorkSheet.Range("A3:A100").Text

but then the $Data variable doesnt show anything, and to be honest this was obtained through searching google so when it doesnt work im stumped :(

Thanks for the responses so far :D

1
excelpowershellcomparespreadsheet
What have you tried so far? SO works better if you show some code for us to help you build on. – Matt
This can totally be done, but Excel scripts are a PITA, so I sure as heck am not just making one up without some visible effort on your part. Please show what you have done (either VBA or PowerShell), and I'm sure we can work something out. Personally I'm thinking reading UserName/Dept to a hashtable, reading logins to objects, add-member dept from HT lookup, pipe to group by department, paste to excel, call it a day. – TheMadTechnician

1 Answers

1
votes

This a a textbook example of when to use vLookup in Excel. Simply open both sheets and use vLookup in the column after the time of login. The username should be the key value and the lookup range will be the other spreadsheet and just use the relative column number for the department field as the third argument. Finally, the 4th argument in the function should be FALSE.