populate a 4th column (D) based on 3 columns (A,B,C)

need to populate a 4th column (D) based on 3 columns (A,B,C)

Columns A and B contain the same # of rows (about 33K) but different values

Column C has about 191K rows (many of which are redundant duplicates)

Column D needs to be based on C where C = B but we use the corresponding value A

Screen shot

I have attempted vlookup, index, and match but have not found the correct formula yet.
Any help is greatly appreciated.

The csv file can be downloaded here: http://www.sharecsv.com/s/d93b55719f...for-forums.csv

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Agree a sample file (not a pic) will help more.

having said that, see if this will help?

=index(A:AC,match(B2,C:C,0))

Is 186,485 lines REALLY a small Excel file?? it doesn't download and doesn't appear to have any expected answers in it (at least not in the tiny preview).

Please re-read post 2.

You have a screen shot of your excel file, so please upload a SMALL CLEAN section of that (less than 100 rows), along with your expected outcome

okay, here is a sample with the 4th column being the expected outcome:

32440 31998 2222 2566
32441 31999 2222 2566
32442 32000 2222 2566
32443 32001 2222 2566
32444 32002 2223 2567
32445 32003 2223 2567
32446 32004 2223 2567
32447 32005 2223 2567
32448 32006 2223 2567
32449 32007 2223 2567
32450 32008 2223 2567
32451 32009 2224 2568
32452 32010 2224 2568
32453 32011 2224 2568
32454 32012 2224 2568
32455 32013 2224 2568
32456 32014 2224 2568
32457 32015 2225 2569
32458 32016 2225 2569
32459 32017 2225 2569
32460 32018 2225 2569
32461 32019 2225 2570
32462 32020 2226 2570
32463 32021 2226 2570
32464 32022 2226 2570
32465 32023 2226 2570
32466 32024 2226 2570
32467 32025 2226 2570
32468 32026 2226 2570
32469 32027 2226 2570
32470 32028 2227 2571
32471 32029 2227 2571
32472 32030 2227 2571
32473 32031 2228 2572
32474 32032 2228 2572
32475 32033 2228 2572
32476 32034 2229 2573
32477 32035 2229 2573
32478 32036 2229 2573

corresponding cLif1QB.png

You have no instances where C=B in your sample?

This is becoming as painful as getting teeth pulled...

Rather than hacking something out of the middle of your sheet, please make up a small sample that both shows your problem and provides the expected answer. Please post it as an Excel sheet. see post 2 for instructions about posting sheets.

Hi,

It was suggested that you upload a section of your file not merely a screens shot. Even so it's not clear to me how you arrive at your expected results. Neiher does the sample accuratelt reflect your production data since column C apparently has many more rows than A & B.

please upload a true rpresentation and explain the calculation of column D.

At the moment the best I can offer is the trivial

D1 = 2566 - I've no idea how you get what looks like a seed value
D2 copied down

Formula:

Please Login or Register  to view this content.

upload a small (clean) representative sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

On a side note, we are not trying to be difficult here, just keep in mind that while you know exactly what you have and what you want, we really hove no clue at all - thats why we need your help, so we can help you

Here's a sample that may/may not reflect your real data. there are 3 instances where C=B. What is the expected answer in column D, and why??

Yea. I can see the csv data. However, you have not shown the expected result or explained how it was derived. The CSV contains ONLY 3 cols.

I haven't looked at every line in detail (life is too short), but I also can't see any instances of B=C... Totally confused...

I haven't a clue what you want. Seventeen posts later, I am none the wiser. Good luck, sorry, but I'm out...

i have, however, drawn attention of other experienced members to your thread in the hope that they can help.

As Ford said back in #13, whilst this is no doubt something you deal with on a daily basis, is meat and drink to you and you understand exactly what the data represents and how you want to manipulate it, we are coming to this cold. We neither know your business, terminology or what you;re trying to do. You really need to help us understand.

There's no point mentioning Maryland Land Records, who I assume provide you with some/all of the data with the implication that we should approach them. If you don't know and can't explain what these sequences represent and why the order is seemingly relevant then how on earyn do you expect us to know. We're often pretty good but not psychic.

You really do need to help us to help you. How difficult is it to:

1. Give us a representative sample of your data which models the relative sizes and order of your data columns
2. Add the results you expect
3. Most importantly tell us how you deduce the results from the data.

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Well that was pretty easy, and Im sure if you had provided us with a representative excel file, and showed what you wanted, we could have solved this for you ages ago.

However, you got your solution (and thats all that really matters, right?), and also, you shared it with us, so Im happy and glad you got where you wanted to be