Replies

I have the same problem – data in Excel that I want to use to print labels, and I have found it easiest to export the data to Word and then use the Word mail merge tools to print off labels. Word (at least Word2K) has templates for most common labels, and allows you to specify a custom template if you are using ones it is not already set up for. I don’t know whether you can save the custom label format, although it would have been a sensible feature for MS to include (insert MS joke here!).

It sounds like you are using billing rates for six different consultants. Although you could nest IF statements six deep, you will be better off using a lookup table: it is much easier to maintain, and easier for a user to follow what is going on.

Post again if you need a hand on using the table, but in short form, you would insert data like this:

     A        B
1  Mike      65
2  Sue       60
3  Dave      75
(etc)

In the location where you needed the rate, you would use a formula like:
=vlookup(CellRef,$A$1:$B$3,2,0), where ‘CellRef’ contains the name of the consultant whose rate you want to apply.

Hope that helps

CPOD’s suggestion to extract date values from the date strings generated in the price file will work, but it doesn’t get you all the way there.

Assuming that you insert the appropriate formula to convert these value to “excel dates” the next step depends on how many items you are dealing with, and how often you will have to do this.

If this is a one-off, and if you are dealing with a small number of item numbers, it will be easiest to take this data file, sort it by item number, then by decending date (in a single sorting operation). Then simply run down the column. Every time the item number changes the first price listed is the most recent price – copy that line to a new location, and you will have the information you need.

If there are a lot of item numbers (as I suspect), then this will be a tedious (and err-prone) process. It will likely be easier to use some Excel functions to extract the data directly.

I would suggest sorting the data in decending date sequence, ignoring the item numbers for now. In the resulting file, the first-encountered instance of each item number will represent the most-recent price. The first step is to extract all unique item-numbers – this is easiest using the Excel Data|Filter|Advanced Filter commmand, with the column of item numbers (including a header) selected. In the Advanced Filter dialogue, specify ‘copy to another location’ and ‘unique records only’ while leaving the criteria entry blank and supplying an approporaite location for the filtered output (I used a cell a couple of rows below the raw data, but that’s your choice). This will write all the unique values into the range including and below the selected output cell.

We can identify the first instance of each item number using the Excel “Match” function, setting it to look for an exact match. The syntax is:
=match(Lookup_value,Lookup_array,Match_type) where
Lookup_value is the value you want to lookup (in this case, each of the unique item numbers);
Lookup_Array is the range where you want to lool up the ‘lookup_value’ (in this case, the range of item numbers in the raw data), and;
Match_type indicates whether you are looking for a value greater than or equal to, less than or equal to, or exactly equal to the Lookup_value. In this case, we want an exact match, and the value should be “0.”
The match function wil return the row within the Lookup_array that contains the first instance of the lookup_value specified. As noted above, that row will represent the most recent price, since the data has been sorted in decending date order.

Assuming that the raw data is in the range C7:C50, and the unique value we want to search for is in cell C54, the formula is:
=MATCH(C54,$C$7:$C$50,0)
using the absolute references as shown allows us to copy the formula down as many rows as are required to match all of the unique values.

Knowing the row in an array that contains a value we want allows us to use the Excel “Index” function to extract a another value in the same row. There are two sytaxes for the Index function, depending on whether you are dealing with multiple areas, but in this case the syntax we want is:
=Index(Array,Row_num,[column_num]) where
Array is the range we want to extract data from (in this case the list of prices, in date order, irrespective of item number;
Row_num is the row number we want to extract a value for (in this case, it will be equal to the value returned by the Match” function above, and;
Col-Num is an otional value, used if the Array has more than one column – in this case it is not needed.

The formula can use a reference to a match function in another column to identify which value to extract, or we can embed that function in the “Index” function itself. Assuming we do the latter, and that the price values are in the range D7:D50 (and other references are as noted above) the formula we woudl use is:
=INDEX($D$7:$D$50,MATCH(C54,$C$7:$C$50,0))
Again – the use of absolute references allows us to copy the formula as required to extract the proce coresponding to each unique value.

I have prepared and attached a small sample s/sheet that shows these formulas, and may be of assistance in understanding this description. Note that the raw data has already been sorted by date, although it started off as a series of random dates and prices. The item numbers are alphabetic for clarity.

Finally, if you are doing this a number of times (say monthly or more often), it may be worthwhile to automate the process completely by writing a macro to extract the date information, sort the data, and insert the required formulas…

The other nice thing about a pivot table is that if you enable ‘drill down’ you can double click on any cell and it wil generate a list of the data elements that gave rise to that total…

Christa:

See the attached s/sheet.

There is a named range called “table” that contains your department codes and descriptions. Elswhere (cell D30) I have entered the formula:

=VLOOKUP(C30,TABLE,2,FALSE)

This will look at the value in C30, and then find a coresponding value in the first column of the “table.” It will then ‘read across’ 2 columns (where the left-most column is #1, not 0) and return the value found in the coresponding cell. The operand “False” tells the function to accept exact matches only – it you leave this out, an entry of -say- “48” (which does not corespond to any department name) would return the name of the last match of a value less than 48 – in this case, department 45 – “Product Marketing, Footwear”

It strikes me that it may be easier to use a “department sequence” – it is simpler to loop through sequences of integers than department numbers. In this case, you woudl establish a table as shown, and use the formulas in cells C32:D32. Putting coresponding formulas in your s/sheet would make the department number and description recalculate each time you changed the sequence number. If that was in a range named “seqRange” your code would be something like:

…
For i = 1 to MaxDept
[seqRange].Value = i

Next i
…

Excel’s behaviour seems a little odd….

I have just filled a cell with 1300+ characters in XL2K, and LEN(xx) reports 1,318 characters. Adding my test string again, LEN(xx) reports 1,421 characters.

Excel only displays the first 1000-odd characters, due to the 1,024 character limitaiton you alluded to, but RIGHT(xx,10) displays the last ten characters as entered in the cell, not the last ten displayed. Functions like FIND() or MID() are able to access and return values from characters in the cell, even the ones after the 1,024 characters displayed. In any event, the cell never displayed asterisks or number signs – it just displays the characters as entered, up to the limit of 1,024.

Even with some non-printing characters (hard carriage returns) I have not been able to duplicate the behaviour originally reported. Maybe I just got a working copy of XL2K?

Sorry – in my last post I misread what you wanted, and thought you just wanted to delete the trailing hyphens.

If you want to replace them with something else the best approach will depend on:

1) Are you going to have to do this many times, or is it a one-off?
2) are you going to replace all the trailing hyphens with the same text, or will there be variations in the text that is appended to the cells?

If this is a one-off, or if you have many different text strings to substitute in, I would would find a couple of unused columns, and put the text you want into one column(on a row-by-row basis). In the next column I would copy in the formula (references assume that target cell is in column A, and the cell you want to substitute in is in column B, and we are entering the formula in row 2):

=if(right(A2,1) = "-",left(A2,len(A2)-1) & B2,A2)

by copying this down all the rows in the table you will have a column that contains the substituted text you want concatenated with the original text (for those cells that had a trailing hyphen) or the original text. Copy this column, and paste/special/value to place these results back in Column A.

If this is going to happen on a recurring basis, and if the text to be substituted is unchanging (or at least, doesn’t change much) you can use the same test for a trailing hyphen that was in the VBA in my last post, but substitute

' NOTE formula corrected from original posting!
Cell.Value = left(CellText, len(CellText) - 1) & "the string you want"  

to concatenate the text you are looking for into the cells with a trailing hyphen

*** Geoff W Long line in “Pre” tag removed- it throws out formatting in the forum ***

I’m glad to hear that the macro did what you wanted!

The ‘undo’ option is not available after a macro has run. The only way around this is to save your sheet immediately prior to running the macro (this can be part of the macro, if you like) and then reverting back to the saved version if you don’t like the effect of the macro.

I don’t know why this is so, or whether it is a deliberate decision by the Excel group at MS, or the inadvertent effect of an otherwise unrelated decision.

attach the following Macro:

Sub WipeData()

Dim Cell As Range
For Each Cell In Selection
   If IsNumeric(Cell.Value) Then
      If Cell.HasFormula = False Then
         Cell.ClearContents
      End If
   End If
Next Cell
End Sub

Select the range where you want to delete the data entered, and then run the macro. If you are doing this often, it may be worth attaching the macro to a button. I like Andrew’s solution, but this requires fewer keystrokes, particularly if you attach it to a button. As well, it does not delete text, in case you have label entries to remind you of assumptions or methods of analysis (‘deduct net non-cash here’ etc)

I would find some empty columns beside the data you have to export.

In three columns type the following formula (or type once, copy twice) in G4:I4

=B4 & REPT(" ",10 - LEN(B4))

(where B4 (and B5 & B6…) is the cell that contains the data you want to export)
in J6:K6 enter

=REPT(" ",15 - LEN(E4)) & E4

(where the data you want to export is in E4:F4)

Thes formulas will return text strings in the format you require. Copy that formula range down as many rows as you have data in, then copy/paste special/value to convert the formulas to their string values, and you can export that portion of the file, rather than your original data.

When the cell was first encountered Excel interpreted it as a text value, due to the leading space, so the LEN() function returned 9, representing 6 digits plus 2 slashes, plus the space. When you removed that leading space Excel defaulted to interpreting the value as a date entry.

The internal representation of a date entry is the number of days since 01/01/1900 (or 1904 – but let’s just leave that alone). For example, Jan 15, 2001, is represented as 36,906 – and this five-digit number returns LEN() of five.

Try:

Sub NoLastHyphen()
Dim CellText As String
Dim Cell As Range
For Each Cell In Selection
   If Cell.HasFormula = False Then
      CellText = Trim(Cell.Value)
      If Right(CellText, 1) = "-" Then
         Cell.Value = Left(CellText, Len(CellText) - 1)
      End If
   End If
Next Cell
End Sub

Select the range you want to clean up, then run the macro. Somebody may be able to suggest better error trapping, and it could run a little faster by not assigning the “cell.value” to a variable, and just dealing with it as “trim(cell.value)” throughout, I suppose….

I suppose I find that I am more likely to move a sheet than to rename it! Even if not, I think that using sheet names is preferable, since it is obvious what you are referring to; a user six months later looking at the code (say to add a sheet to the standard output) will see the names and understand the purpose, where the reference by index number may not tip them off….

If the code is to be used in another workbook, I would almost prefer that the sheetnames be used, since then I KNOW that the code won’t work, and will generate an error (subscript out of range, I expect), rather than ‘sort of’ working by printing sheets other than the ones I want (I can’t imagine that I would want the first, third, fourth and seventh sheets printed in two otherwise-unrelated workbooks!). Since I will have to modify the code anyway, I might as well set myself the task of looking for something meaningful to me, rather than to the computer.

Yup – it seems to work.

Try something like:

    Sheets(Array("Summary", "Registration", "Memberships", "Equipment")).Select
    Sheets("Summary").Activate
    ActiveWindow.SelectedSheets.PrintOut

obviously, you’ll have to change the sheetnames to match your workbook!

and you should get what you need.

If you select the sheets to be printed (ctrl-click for non-adjacent sheets) the total number of pages will calculate correctly so the “page X of Y” will come out properly if you have that set in the footer.

If you do the equivalent action in VBA by adding the required sheets to the selection and then printing the selection, I imagine it would work properly, although I have not tried it. I’m off to waste a couple of sheets of paper – I’ll let you know!