Hi everyone,

Here's another challenge:

On a worksheet, I have a column of consecutive recurring data (meaning
this column is sorted). How do I return the range of a particular
recurring data?


--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

Can you provide an example of your data, showing the state it's in now
and the state you'd like it to be in? For instance, when you say
"return a particular range of data" do you mean data that is between
two values?

The data is like below:

Acct, Qty, Desc, Wt., Rate, Cost
R123, 2, skids, 40, 11%, 10.00
R123, 0, FCS, 0, 15%, 3.00
R321, 1, skid, 100, 11.2%, 21.00
R321, 2, rolls, 100, 11.2%, 21.00
R321, 0, FCS, 0, 15%, 5.00
R654, and so on...

My recurring data is the first column, "R321" and then I want to
extract the Cost of each line that has R321. There is probably two
thousand lines and 500 accounts in each spreadsheet. My boss wants a
report that would get the cost of any given set of accounts that he
specify.


--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

this a continuation of the last post...

The format that my boss wants is below (taking the account R321):

Acct, Qty, Wt., FSC, Cost
R321, 3, 200, 5, 42.00

I can do a VLOOKUP on the first R321 line but I can't get the second or
the third R321 line. I was thinking if there is a formula that would
output the range of R321, I can do a LOOKUP on that range.


--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

Maybe applying data|filter|autofilter would be a way to show/hide the data you
need.

Or maybe doing data|subtotals would be ok???

ledzepe wrote:

The data is like below:

Acct, Qty, Desc, Wt., Rate, Cost
R123, 2, skids, 40, 11%, 10.00
R123, 0, FCS, 0, 15%, 3.00
R321, 1, skid, 100, 11.2%, 21.00
R321, 2, rolls, 100, 11.2%, 21.00
R321, 0, FCS, 0, 15%, 5.00
R654, and so on...

My recurring data is the first column, "R321" and then I want to
extract the Cost of each line that has R321. There is probably two
thousand lines and 500 accounts in each spreadsheet. My boss wants a
report that would get the cost of any given set of accounts that he
specify.

--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

--

Dave Peterson

Autofilter is how I'm doing right now.

If I do a sub-total, is there a way name the range of the result?


--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

The results of a subtotal is that the worksheet has subtotals.

I was suggesting that you use subtotals and the outlining symbols to the left to
show what you want.

ledzepe wrote:

Autofilter is how I'm doing right now.

If I do a sub-total, is there a way name the range of the result?

--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736

--

Dave Peterson

Thanks for the ideas but since the data is in .CSV I made a script to
extract it then re-export the data to a .CSV.


--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513736