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>
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
What about a formula like
=cells(I,Column2) & cells(I,Column3) & cells(I,Column4) & cells(I,Column5)&cells(I,Column6)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

ashbee

New Member
Joined
Sep 16, 2019
Messages
20
What about a formula like
=cells(I,Column2) & cells(I,Column3) & cells(I,Column4) & cells(I,Column5)&cells(I,Column6)

Sorry I'm not quite sure how to use this. I'll try to attach a sample
 

ashbee

New Member
Joined
Sep 16, 2019
Messages
20

ADVERTISEMENT

Ah, okay sorry I thought adding my raw data to the post would be helpful. Here is a link to the shared excel file:

https://gofile.io/?c=9b94Uf
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

ashbee

New Member
Joined
Sep 16, 2019
Messages
20

ADVERTISEMENT

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:

ashbee

New Member
Joined
Sep 16, 2019
Messages
20
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,600
Members
413,917
Latest member
devansh02

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
Top