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.

Re: Rearrange Column To Row Based On Criteria

I'm not. Each item in column A has 0-20 (tops) other numbers associated with it, but there are tens of thousands of those numbers in total. Every value won't be associated with every item.

Does that make more sense?

Re: Rearrange Column To Row Based On Criteria

Sorry, I should have specified: I can't use a pivot table. There are going to be tens of thousands of possible values in total.

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.

Example:

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: Referencing A Sheet Name Entered In A Cell

Does the trick. Thanks!

I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".

Any thoughts? I feel like I'm missing something very basic here.

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

Quote from ByTheCringe2

Actually, I'm being JUST a little over-complicated. What's wrong with

=ROUND(A1*2,1)

Unless I'm set up strangely, if A1 is 4.02, for example, the cell displays "8", not "8.0"

Jim, Could you explain that bit about conditional formatting, please?

Display More

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

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

Quote from ByTheCringe2

You can do it in a formula in the cell. What is the source of the number in the cell?

The number in the cell is the result of an existing formula in that cell. I could always modify the function to include whatever you're proposing...which is...? : D

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

Any ideas? I'm at a loss...

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.

Examples:

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.

I'm new to pivottables and am wondering how to get them to apply functions other than the few listed in the wizard (sum, count, average, etc.) especially for medians and counting non blanks.

Re: Cleaning an imported text file with VBA

Yes yes! That did the trick. Thanks so much! :thanx:

Could you maybe add some comments so I can sort of see what it's doing?

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?