Share via

Moving values from Rows to Columns when duplicate values are found

Anonymous
2013-10-03T13:38:18+00:00

I have 3 columns in my spreadsheet. Column A contains numbers and column B says Default and Column C contains colors.  I have the data sorted by column A.  If rows 2-7 contain the same number, I want to move the corresponding colors in column c to columns D, E, F, G, H.  How can I do this without Copy>Paste>Special Value>Transpose?

Microsoft 365 and Office
Microsoft 365 and Office

A comprehensive suite of productivity tools and cloud services that enhance collaboration, communication, and efficiency. Combining classic Office apps with advanced Microsoft 365 features, it supports both personal and business needs

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2013-10-03T14:14:20+00:00

In D2   =IF(A2=A1,"Delete this row",IF(A2=A3,C3,""))

In E2   =IF(A2=A4,C4,"")

In F2   =IF(A2=A5,C5,"")

In G2   =IF(A2=A6,C6,"")

In H2   =IF(A2=A7,C7,"")

Then copy D2:H2 down to match your list, copy D:H and paste Values, then sort on D and delete any row with Delete this row... and you're done.

Was this answer helpful?

10+ people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102K Reputation points Volunteer Moderator
    2013-10-03T22:53:03+00:00

    Hi,

    You may refer to my solution at this link - http://www.ashishmathur.com/display-text-values-in-the-data-area-of-a-pivot-table/

    Hope this helps.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-10-03T14:20:39+00:00

    I will give it a try, Bernie.  Thank you.

    Was this answer helpful?

    0 comments