Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

:wall:

What data source?

This is more a quality of life issue, really, since I can just force it to change with VBA, but I've encountered this error in both the Connection Definition and the Command Definition, and again both OLEDB and ODBC, once even with just a straight SQL Server connection.

Share this post


Link to post
Share on other sites

Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.

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.

Like do I just show them a formula like this:

{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}

And say, what is this formula doing when copied down a column?

Some questions I initially thought of:

1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etc

Any other good ones?

Share this post


Link to post
Share on other sites

Pivot tables organize and consolidate data.

Simple example. If you have a list of 5,000 cars with colors listed you can create a pivot table and sort by car colors in a seperate table telling you how many colors of each car you have.

Share this post


Link to post
Share on other sites

Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.

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.

Like do I just show them a formula like this:

{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}

And say, what is this formula doing when copied down a column?

Some questions I initially thought of:

1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etc

Any other good ones?

Ask if they know what a Fuzzy lookup is. Serious.

Share this post


Link to post
Share on other sites

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.

  • Like 2

Share this post


Link to post
Share on other sites

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 by Ned

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 ...

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

:blackdot:

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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. :shrug:

Share this post


Link to post
Share on other sites

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. :shrug:

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.

Share this post


Link to post
Share on other sites

I'm sure there's a better way, but:

In cell A1, the text "adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2"

In B1, =LEFT(A1,(FIND(" : ",A1)-1)) (returns "adrarae")

In C1, =RIGHT(A1,LEN(A1)-SEARCH(" : ",A1,1)-2) (returns "arzfdbvae : 32rgewfd : wdewve4fgsww2"

In D1, =LEFT(C1,(FIND(" : ",C1)-1)) (returns "arzfdbvae")

In E1, =RIGHT(C1,LEN(C1)-SEARCH(" : ",C1,1)-2) (returns "32rgewfd : wdewve4fgsww2")

In F1, =LEFT(E1,(FIND(" : ",E1)-1)) (returns "32rgewfd")

In G1, =RIGHT(E1,LEN(E1)-SEARCH(" : ",E1,1)-2) (returns "wdewve4fgsww2")

Hide columns C & E.

I know there's a better way to do that, but it'd take some digging.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Uruk-Hai

Share this post


Link to post
Share on other sites
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

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Insein

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

 

 

  • Like 1

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Let's say A is Model, B is Pcs on hand, and C is Store.  x will be your threshold.  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 by bcdjr1
  • Like 1

Share this post


Link to post
Share on other sites
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. :doh:

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

 

 

Share this post


Link to post
Share on other sites

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!

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.