A few formula questions from 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 X" 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 the column). So if say 'Sheet1'!A2 appears in 'Sheet2'!C:C, return the whole row of data from 'Sheet2' 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 2 days before deadline), but only counting in workdays (so 2 days before Monday is effectively Thursday)

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!

So many questions! It might have been better to put each one in a separate thread, as you are not likely to get answers to all of them in one post.

A fairly quick way to do 1) is to apply autofilter to column D and select the value "X" (or whatever it is) from the filter drop-down. Then you can highlight all the displayed rows and click on delete row. Then select All from the filter drop-down and you will find all the remaining rows will have bunched up.

There are several ways that you could achieve 2). I prefer to use a helper column to identify appropriate records in Sheet2 using a unique sequential number. Then in Sheet1 it is relatively easy to bring those records across. A suitable formula for Sheet2 would be (assuming column H is used for the helper, starting in H2):

=IF(C2=Sheet1!A$2,MAX(H$1:H1)+1,"-")

Copy this down to beyond your data. Then in Sheet1 you can have a formula which finds the rows where those matching records occur, like this:

=IFERROR(MATCH(ROWS($1:1),Sheet2!H:H,0),"")

(suppose that is in B2, copied down). Then it is relatively easy to use an INDEX function to retrieve data from Sheet2 for different columns, with a formula like this:

=IF($B2="","",INDEX(Sheet2!C:C,$B2))

Copy this across and down.

As you say, 3) is similar to 2).

I don't have time to look at 4). Perhaps you should start another thread with just that question and attach a sample workbook to illustrate better what you are trying to achieve (The FAQ describes how to).

Hope this helps.

Pete

I have a list of numbers such as 16, 18, 33,36, and I want to copy these numbers into Excel without the commas. Can that be done? If so how??

@tbitmore5

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Pete