formula help

[deepak bsg]

hello every body
i'm having a problem with formulas, i'm having
a data with sales volumes for
different years and monthwise sales volumes i.e

c.code c.name apr may jun july tot
1010 xxxx 2005-06 10 25 10 25 70
2006-07 20 25 5 10 60
2007-08 30 25 55


1020 xxxx 2005-06 10 25 10 25 70
2006-07 20 25 5 10 60
2007-08 30 25 55

I want to copy the 2007-08 volumes by criteria
c.code. I cannot copy the c.code in to the all relative
years(rows).Alredy i tried hlookup with if. But i'm not
able to get it can any one assistance plz.

Thanks inadvance

 

[Billy Liddel]

Deepak

One way. First you must duplicate the data on each row, eg.

c.code c.name ??? apr may jun july Total
1010 xxxx 2005-06 10 25 10 25 70
1010 xxxx 2006-07 20 25 5 10 60
1010 xxxx 2007-08 30 25 55 110
1020 xxxx 2005-06 10 25 10 25 70
1020 xxxx 2006-07 20 25 5 10 60
1020 xxxx 2007-08 30 25 55 110

Choose, Data, Filter, AutoFilter. Make the selection on the criteria you
want and paste it into another sheet. If this is OK you can improve it later
with macro.

To automate a filters and filtered List see
www.contextures.com/xlautofilter01.html and you should be able to develop a
fully automated routine.

Regards
Peter
=========================

 

[deepak bsg]

sorry billy it is not for my circumstances. it is for analysation of sales.If
u observe the data you can find one thing yearwise & month wise volmues so
that we can analyze the sales comparitive with historical sales of the same
month. And it is a very big data. From that i have extracted some data with
specified classifications ( i.e there is diff type of customers) as per page.
so I cannot do any modifications in the source sheet.

 

[Dave Peterson]

Since you want to copy the data to a different location, you can use Billy's
idea to fill those empty cells.

Then filter to show all rows not equal to the date you want
then delete the visible rows
And remove the filter

It won't take much to fill those cells with the previous values.

See Debra Dalgleish's site for some techniques:
http://contextures.com/xlDataEntry02.html

 

[deepak bsg]

thanks for your suggession. already i used combination with INDEX,MATCH,
OFFSET but i'm not getting the exact value of the perticular cell. see i had
around 4000 rows and 35 columns so changing the data is more difficult. can
you give any assistance plz
thanks inadvance

 

[Billy Liddel]

Yes you can use Indirect and Address to get a reference cell and use Offsets
from there us ing a cell to show the sheet name. But how will you know how
many rows to offset or are they limited? i.e. each c.code can only have say
four values

Perhaps you could copy the data sheet to the end of the book (or to a new
book) and perform your analysis from there

Alternatively, following Dave's suggestion to restore the data to the
original format you could use a macro - something on the lines of

nr = range("A1").currentregion.rows.count
for i = nr to 2 step -1
if cells(i,1)&cells(i,2) = cells(i-1,1)&cells(i-1,2) then
range(cells(i,1),cells(i,2)).delete
end if
next i

Best of luck
Peter