Transposing and clearing cells to tidy up data

ashbee

New Member
Joined
Sep 16, 2019
Messages
20
I’m hoping someone can help – I have used text to columns to split out products that had been entered into one cell and were separated by a comma. I then ran a macro to duplicate the rows based on the number of products to give each product its own row of data.

But now I’m stuck as I don’t know how to clean up the products so that I am left with 1 column that shows each individual product allowing me to delete columns 2-6. So in the example below this would result in a column that contained BIOL23, BIOL33, MICR3, BIOL5 AND MICR2.

I have 5 helper columns because I have instances of up to 5 products entered into one column!
Any advice would be appreciated.

Count
Column2
Column3
Column4
Column5
Column6
Type
Note
EntryID
3
BIOL23
BIOL33
MICR3


PART
Computation
8
3
BIOL23
BIOL33
MICR3


PART
Computation
8
3
BIOL23
BIOL33
MICR3


PART
Computation
8
2
BIOL5
MICR2



IND
Computation3
56
2
BIOL5
MICR2



IND
Computation3
56

<tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
What about a formula like
=cells(I,Column2) & cells(I,Column3) & cells(I,Column4) & cells(I,Column5)&cells(I,Column6)
 
Upvote 0
post your raw data and expected result
or better, post a link to the shared excel file with raw data and expected result
 
Last edited:
Upvote 0
with Power Query aka Get&Transform

TeamPerson1Person2Person3Person4Person5Person6COUNTTypeNoteDateDayTime
A,B,C,D,E,FABCDEF6LAB 01computational biology20-Nov-19Wed10:00
A,B,C,D,E,FABCDEF6LAB 01computational biology20-Nov-19Wed10:00
A,B,C,D,E,FABCDEF6LAB 01computational biology20-Nov-19Wed10:00
A,B,C,D,E,FABCDEF6LAB 01computational biology20-Nov-19Wed10:00
A,B,C,D,E,FABCDEF6LAB 01computational biology20-Nov-19Wed10:00
D,T,ZDTZ3LAB 01gene cloning strategy, group 0223-Oct-19Wed09:00
D,T,ZDTZ3LAB 01gene cloning strategy, group 0223-Oct-19Wed09:00
D,T,ZDTZ3LAB 01gene cloning strategy, group 0223-Oct-19Wed09:00
W,ZWZ2LAB 02Statistics05-Nov-19Tue13:00
W,ZWZ2LAB 02Statistics05-Nov-19Tue13:00
PersonCOUNTTypeNoteDateDayTime
A
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
B
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
C
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
D
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
E
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
F
6​
LAB 01computational biology
20/11/2019​
Wed
10:00:00​
D
3​
LAB 01gene cloning strategy, group 02
23/10/2019​
Wed
09:00:00​
T
3​
LAB 01gene cloning strategy, group 02
23/10/2019​
Wed
09:00:00​
Z
3​
LAB 01gene cloning strategy, group 02
23/10/2019​
Wed
09:00:00​
W
2​
LAB 02Statistics
05/11/2019​
Tue
13:00:00​
Z
2​
LAB 02Statistics
05/11/2019​
Tue
13:00:00​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotOSC = Table.Unpivot(Source, {"Person1", "Person2", "Person3", "Person4", "Person5", "Person6"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(UnpivotOSC,{{"Time", type time}, {"Date", type date}}),
    Reorder = Table.ReorderColumns(Type,{"Team", "Value", "COUNT", "Type", "Note", "Date", "Day", "Time", "Attribute"}),
    ROC = Table.SelectColumns(Reorder,{"Value", "COUNT", "Type", "Note", "Date", "Day", "Time"}),
    Group = Table.Group(ROC, {"Value", "COUNT", "Type", "Note", "Date", "Day", "Time"}, {{"Count.1", each Table.RowCount(_), type number}}),
    Rename = Table.RenameColumns(Table.RemoveColumns(Group,{"Count.1"}),{{"Value", "Person"}})
in
    Rename[/SIZE]

EDIT: This is NOT vba !
 
Last edited:
Upvote 0
with Power Query aka Get&Transform

I haven't used a Power Query before - VBA and Macros have been my go to. Thanks I'll take a look at a beginners guide for that now and see if I can understand where to put the code and what else it might be able to do.
 
Last edited:
Upvote 0
Indeed - there goes my weekend! Thanks Sandy - it looks really good so far. I'd like to understand the basics before trying to see how it can solve the above query so a few hours of reading and watching are planned now. I can see how it might work for so many other things too. Again thanks :)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top