Ask the EXCEL guy ...

[intention]

@rbroome

I will try that. I just laborously pasted special individual cells into a word doc, and opening the word doc was very painful. Each cell had to confirm the virus macro protection, then the linked excel sheet was itself linked to another sheet, which required 3 OKs to open one cell in the word doc. Since I would have had hundreds, it wasn’t the way to go.

I tried turning off all checks and that helped, but I still have to have both spreadsheets open in advance in order to effectively open the word doc.

Perhaps you could try making a summary sheet, with all of the data in one place, so you only have to link to one range on the spreadsheet. Opening both spreadsheets in advance doesn’t seem like much of a problem. With the virus protection turned off, does it load OK?

HTH,

w.

[intention]

@rbroome

Thanks!
That worked as you describe and will help me.
A related question: if I want to use the text in Col A to create a label in Col C, is there a way to do that?

There’s no direct way. What I usually do is copy column A, insert it temporarily to the left of column C, create the names, and delete what I inserted.

w.

[intention]

@rbroome

Thanks

I think I understand that. I have another way to display the last value in a column, which since it is one of the few tricks in Excel I figured out on my own I am inordinately proud of:

INDEX(F10:AZ10,COUNT(F10:AZ10))

This displays the last nonzero number in a row of numbers. Works equally well for columns. The only limitation is that it has to start with a non-blank number in the first cell (f10 in this case) and will work up to the first blank cell.

Probably obvious to all the excel mavins out there, but I like it.

SWEET! Not obvious to me. Always more to learn …

w.

[intention]

@JCorre

How can I have a pivot table count only distinct fields of a given variable?

JCorre, not sure what you are asking. Perhaps a short sample of the data of which you are making a pivot table would help.

All the best,

w.

[JCorre]

@intention

JCorre, not sure what you are asking. Perhaps a short sample of the data of which you are making a pivot table would help.

All the best,

w.

If I’m processing data from a sheet in a pivot table and one of the fields has repeating values, how can I have the pivot table only count distinct values.

For example if one of the columns in the dataset (accounts) is:

account_number
1
2
2
3
3
3
4
4
5
6

A default pivot table count of that variable would be 10 but if I want a distinct count it would be 6.

In sql it would be 'select count(distinct(account_number)) from accounts;

There are multiple field settings in pivot tables (one of which is ‘count’) but there doesn’t seem to be ‘count distinct’.

Does that help? This has been a thorn in my side for a while.

Thanks in advance.

[intention]

@Joey P

I’m not sure what CTRL-1 does, but I’ll try it. About that though, it’s pretty strightfoward, I’ve got two cells, let’s say B1 and B2 both are set to text, B2 contains the function VALUE(B1). If B1 contains “43” so will B2, if it contains “102,3,4” B2 will give me an error. If I think of it, I’ll try putting plain old text (ie “HELLO WORLD”) and we’ll see what happens.

As for the other part…That would probably work, except, I’ve got over a thousand (and counting) files. One or two per day for the last several years. If I could start over (and I’m still debating changing over to this) I would have had one file for each month, with one tab for each day. That way I could have a final tab with totals and/or averages on it. So what I was hoping for is a method, like your’s, but that I could tell excel to find every excel file in a given directory and extract a specific cell out of it.

You can do what you want with macros, but directly, it’s hard. You might take a look at the “INDIRECT” function. If you have the text “B3” in cell A1, then

=INDIRECT(A1)

will return whatever is in B3.

To refer to a closed file, you use something like

=‘Macintosh HD:Users:bob:Desktop:[junk1.xls]Sheet1’!A1

on the Mac, and the same on the PC with slashes for the directory separators.

So in cell C1, put the full directory text

’Macintosh HD:Users:bob:Desktop:

In cell C2, put the workbook name

[junk1.xls]

In cell C3, put the sheet name and cell address

Sheet1’!A1

In cell C4, combine them with the formula

=C1 & C2 & C3 & C4

This gives you the full address of what you want to get, built up out of the component parts. The advantage is that you can change any part, and get a full address out of it.

Then in cell C5, put

=INDIRECT(C4), and it will give you the value of that cell in that file in that directory.

Replicate this horizontally, change the names of the workbooks, and you have it.

Regards,

w.

PS - There’s an easier way than the one TomCat explained for linking to a cell on another worksheet. Simply type “=” into a cell, switch to the other worksheet, and click on the cell you want to link to.

[intention]

Damn … forgot that “colon D” gives a smiley … preview is my friend, when I can remember it.

w.

[intention]

@Joey P

…

About that though, it’s pretty strightfoward, I’ve got two cells, let’s say B1 and B2 both are set to text, B2 contains the function VALUE(B1). If B1 contains “43” so will B2, if it contains “102,3,4” B2 will give me an error. If I think of it, I’ll try putting plain old text (ie “HELLO WORLD”) and we’ll see what happens.

…

Oops, forgot part of your question. In general, there’s no need to use the VALUE function, just use “=B1” instead of “=VALUE(B1)”. You only need the VALUE function if a number is entered as text, and that doesn’t usually happen.

w.

[intention]

Joey, a final note. Not sure why B1 is set to text, but VALUE can only change a number from text to a value. It cannot change a word to a value, because it doesn’t have a value.

If you just want to ignore those cells, use the following in B2:

=IF(ISERROR(VALUE(B1)) , “” , VALUE(B1))

This says if you get an error message, display nothing (""), otherwise display the value. If you want to flag the unsuitable value, replace the “” with something like “ERROR”.

w.

[intention]

@JCorre

If I’m processing data from a sheet in a pivot table and one of the fields has repeating values, how can I have the pivot table only count distinct values.

For example if one of the columns in the dataset (accounts) is:

account_number
1
2
2
3
3
3
4
4
5
6

A default pivot table count of that variable would be 10 but if I want a distinct count it would be 6.

In sql it would be 'select count(distinct(account_number)) from accounts;

There are multiple field settings in pivot tables (one of which is ‘count’) but there doesn’t seem to be ‘count distinct’.

Does that help? This has been a thorn in my side for a while.

Thanks in advance.

I see no way to do this in a pivot table. However, because these kinds of puzzles are my meat, I’ve written you a function to do this. Copy it to a macro sheet in your “Personal Macro Workbook”, and call it by using a statement of the form of either

=COUNTDISTINCT(H3:H15)

or, if you want to count blank cells as a distinct type,

=COUNTDISTINCT(H3:H15,TRUE)

The function counts the number of distinct values in a range. Here’s the function. Everything following an apostrophe (’) is a comment, and is ignored by EXCEL.

Function countdistinct(r As Range, Optional countblanks As Boolean = False) As Long
    'countblanks is an optional parameter, false by default, true if you want to count blank cells as a distinct value
    Dim m, n, ccount As Long
    Dim dupefound As Boolean
    ccount = r.Cells.Count 'get cell count for range "r"
    If r.Cells(1) <> "" Or (r.Cells(1) = "" And countblanks = True) Then 'if first cell is blank, count only if countblanks is true
        countdistinct = 1
    Else
        countdistinct = 0
    End If
    For n = 2 To ccount 'start the loop
        dupefound = False 'set the duplicate flag to false
        If r.Cells(n) = "" And countblanks = False Then
            'do nothing if cell is blank and countblanks is false
        Else
            For m = 1 To n - 1 'inner loop through to count duplicates
                    If r.Cells(m) = r.Cells(n) Then dupefound = True 'if a duplicate is found, set the flag to true
            Next
            If dupefound = False Then countdistinct = countdistinct + 1 'if no duplicates found, add 1 to count
        End If
    Next
End Function

My best to you, if you have further questions, ask’em.

w.

[intention]

Whoa, stop the presses. I just found this function for counting unique values:

=SUM(IF(H13:H19="",0,1/COUNTIF(H13:H19,H13:H19)))

This works provided you don’t want to count blank cells. Replace “H13:H19” with the range of interest in all three places in the formula.

w.

[intention]

Oh, re #51, I forgot to mention that it must be entered as an array formula, by typing in the formula, then holding down the command key (apple key, propellor key) on a Mac, I think its the ALT key on the PC, and then pressing RETURN.

w.

[mamboman]

There has got to be asimple answer to this one, but no one I know can think of it

Say I have a group of 5 numbers, in columns: 1 2 3 4 5

and I want to cut and paste them into rows

1

2

3

4

5

how do it do it?

mm

[intention]

@mamboman

There has got to be asimple answer to this one, but no one I know can think of it

Say I have a group of 5 numbers, in columns: 1 2 3 4 5

and I want to cut and paste them into rows

1

2

3

4

5

how do it do it?

mm

Copy the values, then select the top cell to paste into. Do a “Paste Special” (Edit menu), click “Values” and “Transpose”, and click “OK”.

w.

[Tomcat]

@intention

You can do what you want with macros, but directly, it’s hard. You might take a look at the “INDIRECT” function. If you have the text “B3” in cell A1, then

=INDIRECT(A1)

will return whatever is in B3.

To refer to a closed file, you use something like

=‘Macintosh HD:Users:bob:Desktop:[junk1.xls]Sheet1’!A1

on the Mac, and the same on the PC with slashes for the directory separators.

So in cell C1, put the full directory text

’Macintosh HD:Users:bob:Desktop:

In cell C2, put the workbook name

[junk1.xls]

In cell C3, put the sheet name and cell address

Sheet1’!A1

In cell C4, combine them with the formula

=C1 & C2 & C3 & C4

This gives you the full address of what you want to get, built up out of the component parts. The advantage is that you can change any part, and get a full address out of it.

Then in cell C5, put

=INDIRECT(C4), and it will give you the value of that cell in that file in that directory.

Replicate this horizontally, change the names of the workbooks, and you have it.

Regards,

w.

PS - There’s an easier way than the one TomCat explained for linking to a cell on another worksheet. Simply type “=” into a cell, switch to the other worksheet, and click on the cell you want to link to.

intentionAssuming that the thousands of files are named in a logical, progressive, way (06182001.xls for June 18, 2001, etc)…After the above is done, wouldn’t it be fairly easy to make a macro that would take the list of files and Copy/Paste all the old info into a new workbook and subsequent sheets? Just thinking about the re-doing it all comment: if all the info is on Sheet1 of a thousand different files, it shouldn’t be too hard to grab that info with a FOR/NEXT loop or ??? and make new (monthly) workbooks if the file path is known, the Sheet is known, and the area needing to be copied is known.

This thread is fun - it’s been a while since I’ve really needed to use Excel, I’ve forgotten tons. I used to have hugemongous files with economic data and charts on about 35 countries and various investment products. Automatic updating of chart information, pages of macros and even a GUI system…ahhh, those were the days of late nights and sugar rushes.

Best-
-Tcat

[Joey_P]

@intention

Oops, forgot part of your question. In general, there’s no need to use the VALUE function, just use “=B1” instead of “=VALUE(B1)”. You only need the VALUE function if a number is entered as text, and that doesn’t usually happen.

w.

That worked, thanks.

[intention]

@Tomcat

intentionAssuming that the thousands of files are named in a logical, progressive, way (06182001.xls for June 18, 2001, etc)…After the above is done, wouldn’t it be fairly easy to make a macro that would take the list of files and Copy/Paste all the old info into a new workbook and subsequent sheets? Just thinking about the re-doing it all comment: if all the info is on Sheet1 of a thousand different files, it shouldn’t be too hard to grab that info with a FOR/NEXT loop or ??? and make new (monthly) workbooks if the file path is known, the Sheet is known, and the area needing to be copied is known.

This thread is fun - it’s been a while since I’ve really needed to use Excel, I’ve forgotten tons. I used to have hugemongous files with economic data and charts on about 35 countries and various investment products. Automatic updating of chart information, pages of macros and even a GUI system…ahhh, those were the days of late nights and sugar rushes.

Best-
-Tcat

TomCat, anything can be done with a macro. I was looking for a solution that didn’t require a macro, as most people find them pretty impenetrable. Many thanks for your comments … I need a sugar rush, it’s 2:45 am here.

My best to you,

w.

[ComeToTheDarkSideWeHaveCookies]

My apologies if this may have been answered already.

I’ve been trying to figure out how to use Excel to get around some shortcomings in an SQL querey I have to use to run some reports.

Basically, the SQL querey results include both missing AND redundant data.

For the sake of example, Column A includes Account Numbers, Column B is an “important account factor”, and Column C includes Usernames (belonging to the acct # in Column A).

Acct #,Acct factor,Username
1,b,jon
1,b,sam
1,null,sam
1,null,ben
1,null,tim
2,c,val
2,null,ted
2,c,ted
3,null,ned
3,null,ned
3,null,ken
3,null,liv

What I want the endpoint to be is the basically a lowest common denominator of Column A and Column B. I process Column C with a seperate function, but I need them for analytical context of Column A and B. Not all account numbers have account factors (in the absence of a factor in any “Acct factor” field for any given “Acct #”, null is the valid state). I need to display the appropriate “Acct factor” state for each account number.

This is what I’d want to get out of the above data when all is said and done:

Acct #,Acct factor,Username
1,b,sam (or jon, ben, or tim, but only one)
2,c,val (or ted, but not both)
3,null,ned (or ken, or live, but only one)

This has been driving me CRAZY.

I hope you can help me.

Thanks in advance!

[ComeToTheDarkSideWeHaveCookies]

@ComeToTheDarkSideWeHaveCookies

(in the absence of a factor in any “Acct factor” field for any given “Acct #”, null is the valid state)

:smack:

This should read…

(in the absences of a factor in all “Acct factor” fields for any given “Acct #”, null is the valid state)

[Tomcat]

@intention

TomCat, anything can be done with a macro. I was looking for a solution that didn’t require a macro, as most people find them pretty impenetrable. Many thanks for your comments … I need a sugar rush, it’s 2:45 am here.

My best to you,

w.

Nonono…I’m taking it one step beyond yours. AFTER they do your solution.

@Joey P

As for the other part…That would probably work, except, I’ve got over a thousand (and counting) files. One or two per day for the last several years. If I could start over (and I’m still debating changing over to this) I would have had one file for each month, with one tab for each day. That way I could have a final tab with totals and/or averages on it. So what I was hoping for is a method, like your’s, but that I could tell excel to find every excel file in a given directory and extract a specific cell out of it.

Bolding mine. I was just thinking about re-doing the whole setup and grabbing the old information and putting it into new spreadsheets.

-Tcat