Posts by Flusterphonic

    Re: Rearrange Column To Row Based On Criteria

    [QUOTE=Dave Hawley]So why not a PivotTable?QUOTE]
    Because there are tens of thousands of values, which won't fit on one row. That's precisely the problem. There's not tens of thousands associated with each item, just tens of thousands in total, which a pivot table would try to put across a single row.

    I'm trying to rearrange my data so that a column of info for each unique value in another column becomes a row next to that value.


    A 1
    A 2
    A 3
    A 4
    B 66
    B 77
    B 88
    C 10
    C 15
    C 20
    C 25
    C 30

    should become... (6 columns)

    A 1 2 3 4
    B 66 77 88
    C 10 15 20 25 30

    I had a really hard time figuring out what to search for (and in writing a description) for this one. Thanks in advance for any thoughts/advice.

    Re: Price Discrepancy Analysis

    Quote from Dave Hawley

    I would use a [pt]*[/pt] and on your Source data add some basic IF function to flag YES/NO discrepancies and another to do the math to find out the amount of difference.

    I had that same thought. I just don't know how to approach those functions:

    -I thought about doing a nested IF to check the next items above and below for price discrepency, but sometimes there are a dozen some-odd lines for the same item number which would break the limit on nested functions.

    -I thought about doing a SUMIF()/COUNTIF() to find the average price, and if the average was different than the price on that line, flagging the line as having an item with a price discrepency, but as you can see from the example, that would not always work: it would miss symetrical discrepancies like the last item.

    So I'm back to asking for help. : D

    Basically I'm having trouble writing a function that can identify a price discrepency in a table like the one shown, as well as count the number of unique prices for a given item listed, and a way to calculate the range in those prices.

    I have attatched a sheet that basically lays out the desired input/output, but I really am clueless as to how to approach functions for such a task.

    Any ideas?

    Re: Formatting group of cells to have different decimal rules based on decimal value

    Yeah, simple rounding function works. Can't believe I didnt try that first. :confused:

    Re: Cell containing number and a percentage

    Quote from Ger Plante

    Not that I am aware of.... as you say... this is probably the only solution.

    =B1 & " (" & (B1/A1) * 100 & "%)"

    Yeah, the only issue there is obnoxious decimal places on repeating decimals, etc. Fixed it with TEXT(___,0) formatting (don't need any decimals, really).

    It works. It's just...ugly. :)

    Is there any way to make a cell display both a raw number and a parenthetical representing a percentage based on a formula?

    I know this may sound strange. ;)


    9 (25%)
    12 (39%)

    I could get it done via merging or =_&_ style entries, I know, but is there any other way?

    Re: vlookup with two criteria

    So this implies that it is impossible to do this with a function? I too would like this functionality, but it would be useless unless it were a function that could be copied across a range fo cells. :(

    Re: Selecting rows/columns relative to contents in a macro

    How exactly does that work? I'm guessing Rows.Count implies, uh, counting the number of rows containing data on the active sheet, no? What if there is data below the table somewhere?

    Also, I won't really need to make a seperate sheet from the active. The changes will be made on the active sheet itself, I just made the example two sheets to show the difference, I suppose.

    There may very well be a thread on this already, but I was having some trouble searching for this, as I don't really know how to describe it.

    Basically, I've got these tables and need to write a macro to put a bunch of stuff at the bottom of them, but they vary in terms of how wide (columns) and long (rows) they are every time. I need the macro to skip a row below the bottom of the table and then start putting the stuff in the cells in the next row accordingly. To compound the issue, the "stuff" I need at the bottom uses references and functions that point to the data in the table.

    I've attatched an example, with Sheet1 being the original table and Sheet2 containing the desired outcome. Just keep in mind that the macro I'm trying to make needs to be relative, not tied to that specific table or its dimensions.

    I'm thinking some kind of "trigger word" might be helpful, as the general language and layout of the tables I'm using is fairly consistent. Something like looking for "sum" in the first column to indicate the last row in this example?

    Re: Using pivottables with functions other than wizard options

    I think a big part of my problem so far is that excel seems to refuse to acknowledge the "blankness" of many of the cells.

    The function in the "blank" cells in question returns "", appears blank. When I run COUNT() on the range where they occur, it doesn't count them. Pivottable counts them, though.

    Re: Cleaning an imported text file with VBA

    Quote from norie

    There is absolutely no deletion in that code.

    Yeah, I guess you're right. There's nothing to the effect of telling excel to delete a column, per se-- I suppose it's just telling the data to move to the left?