How do i get data storewise from a Table

[amit dev thapa]

Dear Friends .. Please find attached file .. it is self explanatory once you open the file .. you can see data .. i wanted to get this data place wise

 

[GraH - Guido]

Lay-out is not ideal (merged cells, really? )
I would attack this first with Power Query to have the data structured in a convenient way and then make a simple pivot.

Formula approach is possible. See attached.

 

[rahulshewale1]

Dear @amit dev thapa ,


See if is ok ?


Formula Base solution Using Indirect+Index + name Range


Regard
Rahul shewale

 

[p45cal]

rahulshewale1, your formula in cell D27:
=INDEX(INDIRECT($C27),MATCH($D$24,$C$4:$C$22,0),MATCH(D$26,$D$5:$J$5,0))
refers to $D$24 (a blank cell) whereas I suspect it should refer to $D$25? The same applies to both columns of that results table?

Also the formulae in all the results tables refer to $D$24 whereas I think they should refer to their respective headers in $G$25, $J$25 and $M$25?

 

[amit dev thapa]

Dear @amit dev thapa ,


See if is ok ?


Formula Base solution Using Indirect+Index + name Range


Regard
Rahul shewale

Thanks Rahul it is working . ,since you have define the name for the store name in the same sheet it works perfectly , . but when you create a new file ..it does not work ,. if I dont use the indirect formulae can i use Index and match .. file attached

 

[bosco_yip]

Try to use your original file in post #.01

In C27, formula copied across and down :

=IF(C$26="","",OFFSET($A$4,MATCH(LOOKUP("zz",$C$25:C$25),$B$5:$B$22,0),MATCH($B27,$C$4:$BM$4,0)+MATCH(C$26,$C$5:$I$5,0)))

Regards
Bosco

 

[amit dev thapa]

Thanks Bosco .. it is working perfectly .. couple of query , when i use the same formula in one of the file , but in this file data was starting from A4 .. unlike my old file where data was from B4 , it is working but it is picking different data , hence i guess OFFSET formula is not required .. what can I use

 

[bosco_yip]

Thanks Bosco .. it is working perfectly .. couple of query , when i use the same formula in one of the file , but in this file data was starting from A4 .. unlike my old file where data was from B4 , it is working but it is picking different data , hence i guess OFFSET formula is not required .. what can I use

Should you want to solve the problem, upload your file to the forum is required, then can clear the situation and solve your problem in accordingly.

Regards
Bosco

 

[amit dev thapa]

Ok Please find attached file

 

[bosco_yip]

Ok Please find attached file

In respect of your revised layout table file, the formula only required a bit adjusting (as show in red), then in B27 formula copied across and down :

=IF(B$26="","",OFFSET($A$4,MATCH(LOOKUP("zz",$B$25:B$25),$A$5:$A$22,0),MATCH($A27,$B$4:$BL$4,0)+MATCH(B$26,$B$5:$H$5,0)-1))

Regards
Bosco

 

[amit dev thapa]

In respect of your revised layout table, the formula only required a bit adjusting (as show in red), then in B27 formula copied across and down :

=IF(B$26="","",OFFSET($A$4,MATCH(LOOKUP("zz",$B$25:B$25),$A$5:$A$22,0),MATCH($A27,$B$4:$BL$4,0)+MATCH(B$26,$B$5:$H$5,0)-1))

Regards
Bosco

Thanks Bosco .. much appreciated .. when you are putting -1, is that for column number and one more thing.. what is "zz" for

 

[bosco_yip]

Q1 : when you are putting -1, is that for column number ?

Ans. : Yes, it is only a mathematic calculation for the number of Column in the OFFSET function.

Q2 : what is "zz" for ?

Ans. : This part of formula: LOOKUP("zz",$B$25:B$25) is to extract the header in Row 25, because the headers are in merge cells and cells in C25, F25, I25 and L25 are blank cells or without data.

Here is the example to extract the merge cell header as per below picture.

In B27 enter formula, drag across and your will extract the header
=IF(B$26="","",LOOKUP("zz",$B$25:B$25))

And,

In B28 enter formula, drag across , your will got the row number for the OFFSET function.
=IF(B$26="","",MATCH(LOOKUP("zz",$B$25:B$25),$A$5:$A$22,0))




Regards
Bosco