| Computing desk | ||
|---|---|---|
| < November 13 | << Oct | November | Dec >> | November 15 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
Provided I know the IP of a web-site, but the DNS server is down due to a DoS attack, can I still connect to it? Llaanngg (talk) 03:06, 14 November 2016 (UTC)
I have a two-column chart; most cells in column A are filled with "1", and the remainder with "2". The cells in column B have widely differing values. For example:
I'm trying to write a formula (putting it in the now-unused column D) that will return a result if A=2, and when that's the case, it gets the value in B, finds all the other lines with identical results in B, and places a piece of text in the now-unused column C for all lines where this is the case. In my example text, it would mark lines 1-2 (it finds that A2's value is "2", sees that B2's value is "aa", and notes that A2's value is identical), ignore lines 3-5 (there's no A=2 here), mark lines 7-9 (ditto from 1-2), and I don't care what it would do with line 6, because there are very few spots where A=2 has a unique B value, and I'm setting this up for manual review. I've done a sort by B-then-A values to ensure that a line with A=2 always appears below the lines with A=1 when the B values are identical. The ideal result would be:
I'm familiar with the IF and AND functions, but I don't know how to get a function that finds a group of identical values (without knowing how many there will be) and does something with the lines containing them. I can't simply use the conditional formatting feature and mark all of the duplicates, because that would cause lines 4 and 5 (in my example) to be marked, and I'm doing my best to ignore lines with A=1 when their B values aren't shared with lines where A=2. In a smaller table, I could do the whole thing manually (CTRL+F, go to each A=2 line in turn, and see if it shares a B value with nearby A=1 lines), but there are nearly 850,000 lines in the whole table. Nyttend (talk) 15:57, 14 November 2016 (UTC)
I'm not sure I've really understood your explanation, but I can do what's required with an extra column. Consider this setup:
Column D checks to see if the condition of whether column A has the value of 2 is met and returns the B column value. Column C then uses this to act as an anchor for its lookup function. When I tried it, it returned exactly the findings of your example, but more complicated values might take a little tweaking. Matt Deres (talk) 02:28, 15 November 2016 (UTC)