My data is kind similar to this one, I want to delate the duplicate rows and also merge columns, like below.
I tried to use unique tools, it can delate the identical ones, Joe, but I don't know how to deal with Lisa here
Solved! Go to Solution.
You can use a summarize tool to concatenate the values for Lisa, however you also need to do some cleaning up for Joe (unless you're happy with him having two rows?)
Use a text-to-columns tool to split the Prefer column to rows, then data cleansing to remove leading/trailing white space and convert to title case. Then you can use a unique tool to remove duplicate rows and a summarize tool to concatenate onto a single row per person.
Hi,
Thanks for your reply and it does work well, but I'm very new to this, so get a bit lost here, could you please explain the logic here? I'm not sure the muti-row formula tool
Many thanks
when you use summarize tool to concatenate to one single row for each person, could you explain a bit how did you select columns? I'm trying to do it with hundreds rows and stuck here
Many thanks
@Xiaorui See my workflow attached. For the summarize tool I selected the Code and Name columns at the top (use ctrl to select multiple or you can click and drag), then Group By from the Add dropdown. Then select the Prefer column and go down to String under the Add dropdown to find Concatenate.