Some basic Excel-help for a rookie
Hey guys, I came across this forum and it seems like a nice place to ask those questions a bit too long for a Google search.
I haven't really used Excel much for anything else than simple scatterplots and simple linear regressions.
Now I've come across a task that I'm pretty sure can be done in Excel, but I don't know how to.
I have two large datasets. I need to sort this data in a way that I can't figure out how to do. I'm not expecting someone to do it all for me, but it would be nice with some suggestions as to which formulas I need to check up on.
1) First of all I need to remove all the rows in a dataset, where a specific value appears in a cell. So if "Value B" appears in D:D, remove the whole row (and compress the remaining data so no blank space is left).
2) Next, I need to search for a specific cell value in a column in another sheet and return the whole row of data if the cell value specified appears (and I need to be able to use this function for all the cells in a column). So if say 'Sheet1'!A2 appears in 'Sheet2'!C:C, return the whole row of data which the C-cell with the value is in. This should preferably give a nice dataset with no blank spaces or "0" or whatever.
3) Next thing is probably pretty much the same as the one above: If say 'Sheet1'!B2 appears in 'Sheet2'!E:E, return the value of the neighbour-cell or the cell two columns away. Would be cool if this can be done simultaneously with 2).
4) Then I need to compare the values in a row with a certain benchmark. I have date and time for a given action, and I have a deadline. I need to relate the date and time for the action with the benchmark (3pm on deadline minus 2 days), but only counting in workdays.
I hope some of this makes sense at all. In case it doesn't I'm really sorry to have wasted your time.
Best regards!
1.
1. http://excelexperts.com/xla-routines-eefilterandremove
2. add a calculated column to Sheet1 using the MATCH formula, then filter on that.
then use:
http://excelexperts.com/xla-routines-eefilterandmove
or:
http://excelexperts.com/xla-routines-eefilterandcopytonewsheet
3. use VLOOKUP
4. you can subtract date/times... or use: http://excelexperts.com/xla-routines-eebusinessdaysindaterange