Associate and moving cells together

[Rider_01]

Hello all, I have a long list of part numbers that I am trying to match. I can sort them to match but there is data in a corresponding cell I need to move when I sort and match them. For example:

5 1 a
9 5 c
7 9 d
3 7 b
1 3 e

The second column and third column I need those values to remain together after the sort and look like this:

5 5 c
9 9 d
7 7 b
3 3 e
1 1 a

Is there any way that when I sort and match the first two columns to link that third column data with the second column and have it move when the MATCH function is completed?

[jomaor1]

Hello,

That's...confusing

But what i would do is this:

Create 1 new Row, and do a vlookup. So if you add the column after the last one you have, you would write the formula on cell D2 like this:

Please Login or Register  to view this content.

Assuming you have Headers.

[Rider_01]

After going back and reading that I am not explaining it well, since I am new I can't post a picture but I will try this again....

Part #................. material #.........Location
4.775-054.0..... 6.390-126.0..... P01-M40/1
6.390-126.0..... 8.704-198.0..... P01-S10/WS
8.624-387.0..... 8.705-974.0..... P01-S10/WS
8.704-198.0..... 8.705-993.0..... P01-S40/1
8.705-356.0..... 8.705-994.0..... P01-S40/1
8.705-974.0..... 8.706-141.0..... P01-S40/2



I need to match the material #'s in column B to the part #'s in column A and move the ones that are the same, in the same row. The "location" values in column C are tied with the material # they are beside. When the material # value moves I need the corresponding location # in column C to move with the value it is associated with in column B

I need it to look like this:

Part #............... material #.......... Location
4.775-054.0
6.390-126.0..... 6.390-126.0..... P01-M40/1
8.624-387.0
8.704-198.0..... 8.704-198.0..... P01-S10/WS
8.705-356.0
8.705-974.0..... 8.705-974.0..... P01-S10/WS

I hope this explains it better

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are 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 results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as 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.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Rider_01]

I wanted to post a picture but that wasn't available so here is the sample file. In the "book1" that is the list I am starting with, the
"book2" file is what I need it to look like when I am done. Basically I need to match the material # in column C to the part# in column A and
move it to the cell beside the part# that it matches.

When I move the material# I need that part number beside it in column D to follow the material# to the new location.

[JeteMc]

As shown in columns O:P, formulas that will match material# and location are:
Material#: =IF(ISNUMBER(MATCH(M2,C$2:C$35,0)),M2,"")
Location: =IF(O2="","",INDEX(D$2:D$35,MATCH(O2,C$2:C$35,0)))
Let us know if you have any questions.

[Olly]

You could use Power Query. Format your data in A1:D35 as a table, then use:

Please Login or Register  to view this content.

[Rider_01]

As shown in columns O:P, formulas that will match material# and location are:
Material#: =IF(ISNUMBER(MATCH(M2,C$2:C$35,0)),M2,"")
Location: =IF(O2="","",INDEX(D$2:D$35,MATCH(O2,C$2:C$35,0)))
Let us know if you have any questions.

AWESOME!!! Thank you, this worked perfectly. Just saved me from having to match and move 32,000 part numbers.

Edit: Both of these solutions work, thanks guys you were a big help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment of the time that was saved to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.