Copy all cells with data to a column.

[craigwojo]

Hi everyone,

This is part 1 of a project I am trying to grasp for making lockout/tagout tags. I was given this spreadsheet with all this data. All I need to do is take all the cells that have data and put them in (column M) (in no particular order) with no black cells in the column.
How can this be done?

I'm using XL2BB to paste the sheet here. I am pasting just a section because it's larger than 3000 cells (which XL2BB does not copy over 3000 cells). The original spreadsheet has data for ROW 618.

Leprino Foods Tags Order Motor Numbers.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	E671	E670
3	E672 PW01-UP01	E673 PW01-UP02	E674 PW01-UP03
4	E675 GC03-UP01	E676 HPW01-PP01
5	E677	E695 PW01-UP01
6	E678 TD01-UP01	E679 TD01-UP02	E680 TD01-UP03
7	E681 TW01-UP01	E682 TW01-UP02	E683 TW01-UP03
8	E684 HW01-UP01	E685 HW01-UP02
9	E686 TM02-UP01	E687 TM02-UP02
10	E688 AC01-UP01	E689 TM02-UP07
11	E690 TM01-UP01	E691 TM01-UP02	E692 TM01-UP03
12	E693 HP01-UP01	E694 HP01-UP02
13	E696 TM01-UP04	E697 TM01-UP05
14	E698 TM02-UP03	E699 TM02-UP04
15	E700 HPW01-UP02
16	E701 DA01-UP06	E702 DA01-UP07
17	E703 DA01-UP01	E704 DA01-UP02	E705 DA01-UP03	E706 DA01-UP04
18	E707 WH01-CO01
19	E708
20	E586 DBX01	E587 DBX01-CR01-MT01	E588 DBX01-CR02-MT01
21	E589 CU17-CR02-MT01	E590 CU17-CR01-MT01	E591 CU17-MAIN	E592 CU17-BELT	E593 CU17-CC01-MT01	E594
22	E595	E596 CU18-CR04	E597 CU18-CR02	E598 CU18-CR03	E599 CU18-CR01	E600 CU18-MT01	E601 CU18-MT02	E602 CU18-MT03	E603 CU18-CR05
23	E604 CU19-CC01	E605 CU19-MT01	E606 CU19-MT02	E607 CU19-MT03	E608 CU19-CR01	E609 CU19-CR02	E610 CU19-CR03	E611 CU19-CC03	E612 CU19-CC02
24	E613 DI13-BK01	E614 DI13-MT01	E615 DI13-MT02
25	E616 DI14-MT01	E617 DI14-MT02	E618 DI14-BK01
26	E619 SH13-MT01
27	E620 SH14-MT01
28	E621 FI06-CV02-MT01	E622 FI06-TU01-MT01	E623 FI06-CV01-MT01	E624 FI06-IA01-MT01	E625 FI06-IA01-AG01	E626		E627		E628	E629	E630
29	E631
30	E632	E633
31	E634		E635
32	E636
33	E637 CA05-FG01	E638 CA05-FG02	E639 CA05-FG03	E640 CA05-FG04	E641 MTR 14201	E642
34	E643 MTR 10613	E644 MTR 10703	E645 MTR 10713	E646 MTR 0713	E647 MTR 10803
35	E648	E649	E650	E651 MTR 0903	E652 MTR 0613
36	E653	E654
37	E655	E656 MTR 0913
38	E657						E658	E659
39	E709
40	E710 DM02-RP01
41	E711
42	E712 DM04-PP08	E713 DM01-PP08	E714 CI13-RP03	E715 DM01-AG21	E716 DM01-AG22
43	E717 DM07-PP08	E718 CI14-RP03	E719 DM04-AG23	E721 DM04-AG21	E722 DM04-PP01
44	E723 DM05-RP02
45	E724 DM06-CO01
46	E725
47	E726 FH01-UP01
48	E727 FH02-UP01
49	E728 FS10
50	E729 CO01-PP01
51	E730 HW01-UP01
52	E731 HT40-UP01
53	E732 P201-CO01
54	E733 P201-PP07	E734 P201-PP06	E735 CI70-RP04
55	E736 P201-PP03
56	E737 P201-UP01	E738 P201-PP02	E739 CI70-RP02	E740 CI70-RP01
57	E741 P202-PP07	E742 P202-PP06	E743 CI71-RP04
58	E744 P202-PP03
Lemoore West

 

[alansidman]

Using Power Query Unpivot the columns and close and load to column M

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

 

[craigwojo]

Using Power Query Unpivot the columns and close and load to column M

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Sorry alansidman, I don't understand.
Should I paste this code in VBA module?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[craigwojo]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Hi,
I put the Power Query code you made for me and put it in the "Advanced Editor" in "Get Data".
I get...
= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1

What is "Table1"? Is it the name of the file?, The tab name? What should I change the Table1 to?

 

[alansidman]

You have a range of data starting in A2. Highlight the entire range. Press Control and T. That will create a table. It should be Table 1. Then apply the Mcode.

 

[Peter_SSs]

You could just put this formula in, say, cell M1. Adjust the range to your larger range as required

=TOCOL(A1:L58,1)

 

[craigwojo]

Thank you everyone for all your help and solutions. God bless you all.
Craig

 

[Peter_SSs]

You're welcome. Glad the formula seems to have worked for you. Thanks for the follow-up.