***Official*** Excel Help Corner

[Ignoratio Elenchi 2,452]

Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?

This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.

At one point when I interviewed for my last job a few years ago, the interviewer just handed me her laptop with an Excel file opened and asked me some questions about the data which I could use any method to answer. I don't remember the specifics but it was like a table of data, 10 columns and 100,000 rows or whatever, and she asked stuff like "how many customers have a join date before June 2010?", "what percentage of our total sales came from customers in NY?", etc. It was nonsense data, the point was just to see how comfortable I was with using Excel and how quickly and efficiently I could extract those kinds of answers from the data. I thought it was great. Rather than asking me boring questions about vlookup and index/match and PivotTables and whatnot, I was free to demonstrate that I knew how to use those things.

[Ned 10,758]

Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?

This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.

At one point when I interviewed for my last job a few years ago, the interviewer just handed me her laptop with an Excel file opened and asked me some questions about the data which I could use any method to answer. I don't remember the specifics but it was like a table of data, 10 columns and 100,000 rows or whatever, and she asked stuff like "how many customers have a join date before June 2010?", "what percentage of our total sales came from customers in NY?", etc. It was nonsense data, the point was just to see how comfortable I was with using Excel and how quickly and efficiently I could extract those kinds of answers from the data. I thought it was great. Rather than asking me boring questions about vlookup and index/match and PivotTables and whatnot, I was free to demonstrate that I knew how to use those things.

That's pretty awesome. I wish I had gotten that in one of my interviews.

Edited January 20, 2016 by Ned

[dino259 633]

Say I have 100 to 1000 rows of data. Is there an easy way to insert 10 or 11 empty rows in between them all?

[Binky The Doormat 13,108]

Say I have 100 to 1000 rows of data. Is there an easy way to insert 10 or 11 empty rows in between them all?

Probably clumsy, but I would just right click the row where I wanted to insert the rows and start with the first one and then copy and paste the others. First 2 then 4 ...

[TheIronSheik 8,168]

OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:

I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2

There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.

How do I go about this?

[TheIronSheik 8,168]

OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:

I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2

There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.

How do I go about this?

Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?

No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.

I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells.

[Ned 10,758]

OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:

I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2

There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.

How do I go about this?

Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?

No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.

I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells.

This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.

I threw it together quickly, so make sure to test it before you trust it.

Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))

Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)

[AcerFC 4,915]

Is Excel and google sheets similar in what scripts you can write

I am interested in learning about formulas and scripts. Where would you suggest I go to learn.

[Ned 10,758]

Google sheets is like Excel Lite. Some formulas are the same, but Excel is much more powerful. I don't think you can macro in Google Sheets, but I've never looked either.

Get Excel and do some reading online. It's easy stuff.

There are also some good trouble shooting tips burred in this thread if you want to read through it all.

[Insein 11,181]

I'm an excel amateur. Well above the masses but still below expert level. I like checking in here to see what the experts like to do.

[TheIronSheik 8,168]

OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:

I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2

There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.

How do I go about this?

Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?

No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.

I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells.

This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.

I threw it together quickly, so make sure to test it before you trust it.

Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))

Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)

What are the A1, B1 and C1's in your formulas above?

[Ned 10,758]

OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:

I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2

There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.

How do I go about this?

Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?

No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.

I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells.

This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.

I threw it together quickly, so make sure to test it before you trust it.

Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))

Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))

Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)

What are the A1, B1 and C1's in your formulas above?

Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2.

So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above).

Clear as mud!

[TheIronSheik 8,168]

Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2.

So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above).

Clear as mud!

OK. That's what I thought. Which means it's not working for me.

First column comes out fine. Next column, not so much. Sometimes I get 2 quartets in the cell. Sometimes I get 2 and a half quartets. My first guess is that I'm doing something wrong, as I have total trust in you. But I'm pretty much just copying and pasting, then changing the A1's and such.

[Ignoratio Elenchi 2,452]

Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2.

So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above).

Clear as mud!

OK. That's what I thought. Which means it's not working for me.

First column comes out fine. Next column, not so much. Sometimes I get 2 quartets in the cell. Sometimes I get 2 and a half quartets. My first guess is that I'm doing something wrong, as I have total trust in you. But I'm pretty much just copying and pasting, then changing the A1's and such.

Something like this should work:

=TRIM(LEFT($A1,FIND(":",$A1)-2))

=TRIM(MID($A1,FIND(":",$A1)+1,FIND(":",$A1,FIND(":",$A1)+1)-FIND(":",$A1)-1))

=TRIM(MID($A1,FIND(":",$A1,FIND(":",$A1)+1)+1,FIND(":",$A1,FIND(":",$A1,FIND(":",$A1)+1)+1)-FIND(":",$A1,FIND(":",$A1)+1)-1))

=TRIM(RIGHT($A1,LEN($A1)-FIND(":",$A1,FIND(":",$A1,FIND(":",$A1)+1)+1)))

It's ugly, but once it's done it's done. The alternative is to write something more robust in VBA, which may or may not be overkill.

[TheIronSheik 8,168]

Thanks, everyone! I'll have to give these a try Monday morning. I don't mind if they're overkill. Most of my formulas end up being like that anyway. In the long run, it works out to be time saving. I run these reports each week and by doing it this way, it saves me hours of work.

[Brony 7,758]

"wdewve4fgsww2" was my high school nickname.

[Ned 10,758]

Thanks, everyone! I'll have to give these a try Monday morning. I don't mind if they're overkill. Most of my formulas end up being like that anyway. In the long run, it works out to be time saving. I run these reports each week and by doing it this way, it saves me hours of work.

Was your example an exact layout? It worked when I wrote it.

[VA703 924]

is there a formula or rule I can apply that will change all cells with the value of 0 to a certain background color?

[BroadwayG 1,011]

Conditional formatting

[Uruk-Hai 7,685]

I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically?

 

eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row.

Edited March 28, 2016 by Uruk-Hai

[acarey50 701]

3 hours ago, Uruk-Hai said:

I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically?

 

eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row.

There is no built in way to sort alphabetically, but this macro from Microsoft should sort all tabs alphabetically.  https://support.microsoft.com/en-us/kb/812386

As for inserting the tab into the correct spot, simply right click on the tab name to the right of where you want to insert a new tab, click on insert and then click on "Worksheet", and you will get a new blank tab to the right of the tab you right clicked on

[Mr. Ected 2,901]

Using Excel 2011 and 2016, having issues with time and dates. When I go to put the time ( [cmd] + [shift] + [;] ), I have no problems. When I try to input a date ( [cmd] + [;] ) I get the time also. Very strange.

[Insein 11,181]

I've exhausted my mental capabilities on Google search and can't figure out this simple error.

I keep getting "Insert into statement contains the following unknown field: 'F1'" for the VBA code I have.

I'm trying to do a simple append of excel data to an existing access table. That error would suggest that one of my field names is wrong but everyone of them is EXACTLY the same as the DB. I also tried to see if there were hidden fields or extra data beyond the main but nothing else exists.

I'm stumped. Anyone else run into this before?

Quote

Set cn = CreateObject("ADODB.Connection")

    dbPath = "S:\Compliance Reporting\Databases\Records Review Database.accdb"

 

    dbWb = RFile

 

    dbWs = "Sheet1"

 

    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

 

    dsh = "[" & dbWs & "$]"

 

    cn.Open scn

 

    ssql = "INSERT INTO tblSalesRoster "

 

    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh

 

 

 

    cn.Execute ssql

 

    cn.Close

Edited April 11, 2016 by Insein

[bcdjr1 687]

7 hours ago, Insein said:

I've exhausted my mental capabilities on Google search and can't figure out this simple error.

I keep getting "Insert into statement contains the following unknown field: 'F1'" for the VBA code I have.

I'm trying to do a simple append of excel data to an existing access table. That error would suggest that one of my field names is wrong but everyone of them is EXACTLY the same as the DB. I also tried to see if there were hidden fields or extra data beyond the main but nothing else exists.

I'm stumped. Anyone else run into this before?

Can you put a sample of the table you're trying to insert into and the sheet you're inserting from?

If I had to guess, I would think that you may be trying to insert more fields from the worksheet than you have fields in your table.

[Insein 11,181]

8 minutes ago, bcdjr1 said:

Can you put a sample of the table you're trying to insert into and the sheet you're inserting from?

If I had to guess, I would think that you may be trying to insert more fields from the worksheet than you have fields in your table.

I thought that too. I'll double check again tomorrow but I copied a line from the table and pasted into excel to match up the headers and they were exact, even spelling. Which makes sense since I built the original table from one of these files.

[bcdjr1 687]

7 hours ago, Insein said:

I thought that too. I'll double check again tomorrow but I copied a line from the table and pasted into excel to match up the headers and they were exact, even spelling. Which makes sense since I built the original table from one of these files.

You aren't specifying a set of columns from the worksheet in your code, so I'm thinking that maybe it's picking up an extra column and then failing when trying to load that into your table.

[Thorpe 230]

Change it to this, which will create the table.

    ssql =  "SELECT *  INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh

 

Then compare tblSalesRoster_tmp  with tblSalesRoster.

 

You should then list the field names in the Select and the Insert.

 

 

[Insein 11,181]

2 hours ago, Thorpe said:

Change it to this, which will create the table.

    ssql =  "SELECT *  INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh

 

Then compare tblSalesRoster_tmp  with tblSalesRoster.

 

You should then list the field names in the Select and the Insert.

 

 

I'll give this a shot this afternoon.

[Insein 11,181]

4 hours ago, Thorpe said:

Change it to this, which will create the table.

    ssql =  "SELECT *  INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh

 

Then compare tblSalesRoster_tmp  with tblSalesRoster.

 

You should then list the field names in the Select and the Insert.

 

 

So this found my problem. The source file has the top 2 rows as blank space with one cell having text in b2. In my macro, I select the rows, clear all and then delete them.  The saved file has no space in the top two rows. I save and close. Yet when I send it to access, the table has the first 2 rows as blank aside from the one cell. Now I'll have to figure out how to fix this.

[Gawain 2,372]

I have a list of models.

I receive a list of all models with on hands for each model by store.

There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold.

I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions?

[Insein 11,181]

21 minutes ago, Gawain said:

I have a list of models.

I receive a list of all models with on hands for each model by store.

There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold.

I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions?

What are the headers in your list?

Model-Amount-Store

[Ned 10,758]

40 minutes ago, Gawain said:

I have a list of models.

I receive a list of all models with on hands for each model by store.

There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold.

I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions?

Create a pivot table and then use the filters (in the pivot) to analyze what you want. 

[Gawain 2,372]

3 hours ago, Insein said:

What are the headers in your list?

Model-Amount-Store

Pretty much:

Model-Pcs on hand-Store (and a few hundred other columns) against a static model list.

I can use a pivot table, but I either have to manipulate the way the data comes, or select everything.

[bcdjr1 687]

Let's say A is Model, B is Pcs on hand, and C is Store.  x will be your threshold.  y is the number of rows on your sheet.  You will have to substitute for x and y in your code.

=IF(B1 < x, C1, "") goes in column D.  If your on-hand is less than your threshold, you'll get your store number or name, if not you'll get a blank.

Then in a single cell at the bottom, put =SUM(IF(FREQUENCY(IF(LEN(D1:Dy)>0,MATCH(D1:Dy,D1:Dy,0),""), IF(LEN(D1:Dy)>0,MATCH(D1:Dy,D1:Dy,0),""))>0,1))

The last function will be an array formula, so after you put it in you'll get #VALUE!, and you'll have to go to the cell, hit F2, and then CTRL+SHIFT+ENTER.

I got the last part of this from this URL on the office help pages.  It has an explanation of how it works there.

 

Edited May 20, 2016 by bcdjr1

[Gawain 2,372]

Thanks, I'll give it a whirl when I get in tomorrow.

[Ned 10,758]

10 hours ago, Gawain said:

Pretty much:

Model-Pcs on hand-Store (and a few hundred other columns) against a static model list.

I can use a pivot table, but I either have to manipulate the way the data comes, or select everything.

I misread what you were looking to do.

@bcdjr1's solution is great. Turn on filters if you wanted to look at the list of stores (filter on the helper column D) that are under-stocked. 

 

 

[Gawain 2,372]

I wasn't able to get the array function to work, but I revisited the Pivot Table idea and was able to make this work smoothly. (countif off of the table)

Thanks for the help!

[Insein 11,181]

I usually avoid the formulas. Just my preference. I like doing it all with VBA because it takes up less space when you save. If it's small, that doesn't matter but when you get up to 10k+ lines, that many formulas can balloon a File size.

[roadkill1292 10,469]

On 3/28/2016 at 7:36 AM, Uruk-Hai said:

I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically?

 

eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row.

Is it too simple to suggest doing Move or Copy? Right click on the tab and you can place it wherever you want.

Every time I try to answer one of these help questions I find out I'm actually years behind what everybody else already knows and assumes. I have no idea where your Excel skills are, gb, just trying to help.

[Uruk-Hai 7,685]

13 minutes ago, roadkill1292 said:

Is it too simple to suggest doing Move or Copy? Right click on the tab and you can place it wherever you want.

Every time I try to answer one of these help questions I find out I'm actually years behind what everybody else already knows and assumes. I have no idea where your Excel skills are, gb, just trying to help.

No, that's exactly what I needed to do. Been using Excel for a million years and it somehow never came up for me before then. Anyway, someone showed me the light, but I appreciate you responding GB.

[Long Ball Larry 14,312]

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

[Ned 10,758]

41 minutes ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

In column 61..... =CONCATENATE(your cells) and drag down through the last row. 

[Long Ball Larry 14,312]

37 minutes ago, Walking Boot said:

Including duplicates?

There should not be duplicates, though if there are it is fine.

[Long Ball Larry 14,312]

3 minutes ago, Ned said:

In column 61..... =CONCATENATE(your cells) and drag down through the last row. 

Won't that keep them in their current rows?  I need to stack them all on top of each other in 1 column.

[Ned 10,758]

21 minutes ago, Long Ball Larry said:

Won't that keep them in their current rows?  I need to stack them all on top of each other in 1 column.

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

Edited May 25, 2016 by Ned

[Long Ball Larry 14,312]

Just now, Ned said:

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

START

Column A               Column B             Column C

Dog                    Eagle                Tiger

Cat                    Fish                 Lion

Bear                                        Muskrat

Weasel


RESULT

Column A

Dog
Cat
Bear
Weasel
Eagle
Fish
Tiger
Lion
Muskrat

 

[bcdjr1 687]

2 minutes ago, Ned said:

Yeah they'd all be in one column by 500 rows.....A B C ABC

What do you mean by "stack them all on top of each other in one column"?

I think he wants a list of all the values from each of the columns in a single column.  Like a master list of values.  Not all the values for each column concatenated together.

[bcdjr1 687]

1 hour ago, Long Ball Larry said:

Question:

I have a sheet with about 60 columns in it.  Each column has a list of somewhere between 1 and about 500 values.  I need to create one column with all of the values from each column.  Thoughts?

Do you want a static list or dynamic?  If static, just copy and paste the values from each of the 60 columns into a single column, resulting in one column with about 3000 rows.  Then, if you want to remove the duplicates, you can highlight the column, click on the Data menu and then use the Remove Duplicates function.

If you want a dynamic list and you don't mind duplicates, you can create a column that has as its first 500 rows a reference to the same cell in column A.  So for the first row, it'd be =A1, the second row would be =A2, etc. down to =A500.  Then, for row 501, you put =B1, do the same as for the A values, and repeat for all 60 columns.

[Long Ball Larry 14,312]

Just now, bcdjr1 said:

Do you want a static list or dynamic?  If static, just copy and paste the values from each of the 60 columns into a single column, resulting in one column with about 3000 rows.  Then, if you want to remove the duplicates, you can highlight the column, click on the Data menu and then use the Remove Duplicates function.

If you want a dynamic list and you don't mind duplicates, you can create a column that has as its first 500 rows a reference to the same cell in column A.  So for the first row, it'd be =A1, the second row would be =A2, etc. down to =A500.  Then, for row 501, you put =B1, do the same as for the A values, and repeat for all 60 columns.

static, and yes, I realize that it could be cut and pasted, but I figured there should be a faster way.

[bcdjr1 687]

Just now, Long Ball Larry said:

static, and yes, I realize that it could be cut and pasted, but I figured there should be a faster way.

That's pretty fast.  I could do it in >5 minutes.