Link with data in master column within sheet

It's not very easy to see which data is in which columns - perhaps you could attach a sample workbook (the FAQ describes how) with examples of what you have and what you want to do with it.

Pete

Hi Pete,

Thanks for the quick reply!

I have attached a sample workbook. What I am trying is to Match / Sort the sheet on column B and D.
If I can couple them I know where column A is connected via column G. I was also thinking to out A and B in a separate sheet, and D to H in another sheet. And then the combined output in a third sheet....

If you could help that would be great.

Thanks, Patrick

I'm a bit confused with this. Your first post referred to columns B and C, but column C is empty in your attached file. Also, you have some records with missing data in columns D to I.

I don't know what you mean by "Match / Sort" on columns B and D. Do you mean you want to see if any of column D entries match with column B (or vice-versa), or do you mean that you want to sort the data using columns B and D as the sort fields?

Please try to explain more clearly what you want to end up with.

Pete

Hi,
In this sheet column C is empty because of a try-out. What I am trying is the following: A and B are the master records, which is a long list. What I tried is to take the first record in D1, when this record also exist in column B then take the record from A and put it in column C.

So f.e. D3=001a.4bbc.606e, this also exist in B120 and the data in A120 is 192.61.22.168 and this gets copied to C3.
The end result would be: 192.61.22.168 001a.4bbc.606e GigabitEthernet4/2 USDPKES101 Gi1/13 ES181

The left A and B columns have the long list with the complete data and for the columns from D to the right I need to make a link with column A via B.

Hope this clears what I am trying...

Thanks, Patrick

PS just saw that I forgot to remove the duplicates from A and B, these will be removed. is not relevant.

Okay, put this formula in C1:

Formula:

Please Login or Register  to view this content.

then copy down as far as you have data in column D.

Hope this helps.

Pete

Isn't this exactly the same post as post #6, and didn't I answer it with post #7 ?

Pete

eh, maybe it's me...
where do I find the number of the post?

It's in the top right corner of the post - opposite the date and time.

Pete

:-) within this post....

don't know buy my reply was posted twice!

I tried it with other items, but IFERROR...
Great thanks, it works!

Is it possible to also combine it with a vlookup?
The outcome of this formula in D1, to compare the value in column A of another sheet. If yes, put the value of that column B in this sheet at column I?

Thanks, Patrick

Something like this in cell i1:

=IFERROR(VLOOKUP(D1,'sheet name'!A:B,2,0)),"")

Put the name of the sheet in the formula, then copy down.

Hope this helps.

Pete

Hi,

There is still something missing! I changed it to:
=IFERROR(VLOOKUP(C1,Sheet2!A:B,2,0),"")

So the data in C is compared with A in sheet 2 and when matched the data from sheet2 B goes to I...

But I don't get the output...

Thanks, Patrick

That's because there are no matches with column A of Sheet2. Apply the filter to column C and you will see that the values vary from 192.61.22.67 and the final number increases up to 255, but in column A the largest number is 192.61.22.64, so there is no match. Put some more data in Sheet2 and ensure that you copy the formula in i1 down the column.

Hope this helps.

Pete

Hi,

Thanks a lot! Everything works now, removed one ) and put some more data in the second sheet.

Thanks again...

Regards, Patrick