Rows columns

[alliswell]

Hi everybody !

Can we use Rows and Columns function together in nested formula to extract data ? if yes, how to ?

Thanks & Regards

 

[Joe4]

There are lots of questions, such as where does the information you are going to plug into said formula come from.
So can you show us an example of exactly what it is you are trying to do? I think that would go a long way in trying to come up with a solution to do what you need.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alliswell]

There are lots of questions, such as where does the information you are going to plug into said formula come from.
So can you show us an example of exactly what it is you are trying to do? I think that would go a long way in trying to come up with a solution to do what you need.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Mr joe, thanks for replying, just check the attachment.

There are various ways to extract data, but i need with rows and columns to extract data as statement of customers.

Thanks and regards

 

[alliswell]

No reply yet !

 

[Fluff]

In future please do not mark a post as the solution when it does not contain one. Especially when you are still looking for help.

 

[Peter_SSs]

just check the attachment.

Sure, we can check the attachment to get an idea, but we cannot copy from it to test formulas.
For the future, please review Joe's comments about XL2BB. As well as making it easier for helpers, it will likely get you much faster responses.

Try a formula like this in A34 and drag down and across.

25 01 12.xlsm
	A	B	C	D	E
1
2		Name 4	Product 4	201	892
3		Name 3	Product 12	370	915
4		Name 2	Product 10	149	53
5		Name 3	Product 20	620	797
6		Name 8	Product 22	303	350
7		Name 5	Product 14	87	307
8		Name 3	Product 23	935	434
9		Name 9	Product 12	47	878
10		Name 2	Product 18	870	712
11		Name 1	Product 22	916	855
12		Name 2	Product 25	430	551
13		Name 2	Product 3	99	670
14		Name 9	Product 20	521	541
15		Name 1	Product 20	5	432
16		Name 3	Product 17	601	31
17		Name 7	Product 2	142	488
18		Name 9	Product 3	11	678
19		Name 8	Product 17	719	486
20		Name 8	Product 23	734	972
21		Name 8	Product 21	420	357
22		Name 1	Product 15	142	575
23		Name 4	Product 2	999	828
24		Name 3	Product 26	913	778
25		Name 4	Product 3	258	63
26		Name 6	Product 17	227	783
27		Name 4	Product 3	493	767
28		Name 6	Product 2	737	548
29		Name 4	Product 19	620	391
30
31	To Search
32	Name 2
33
34	Name 2	Product 10	149	53
35	Name 2	Product 18	870	712
36	Name 2	Product 25	430	551
37	Name 2	Product 3	99	670
38
Extract

Cell Formulas
Range	Formula
A34:D38	A34	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$29)/($B$2:$B$29=$A$32),ROWS(A$34:A34))),"")

 

[alliswell]

Sure, we can check the attachment to get an idea, but we cannot copy from it to test formulas.
For the future, please review Joe's comments about XL2BB. As well as making it easier for helpers, it will likely get you much faster responses.

Try a formula like this in A34 and drag down and across.

25 01 12.xlsm
	A	B	C	D	E
1
2		Name 4	Product 4	201	892
3		Name 3	Product 12	370	915
4		Name 2	Product 10	149	53
5		Name 3	Product 20	620	797
6		Name 8	Product 22	303	350
7		Name 5	Product 14	87	307
8		Name 3	Product 23	935	434
9		Name 9	Product 12	47	878
10		Name 2	Product 18	870	712
11		Name 1	Product 22	916	855
12		Name 2	Product 25	430	551
13		Name 2	Product 3	99	670
14		Name 9	Product 20	521	541
15		Name 1	Product 20	5	432
16		Name 3	Product 17	601	31
17		Name 7	Product 2	142	488
18		Name 9	Product 3	11	678
19		Name 8	Product 17	719	486
20		Name 8	Product 23	734	972
21		Name 8	Product 21	420	357
22		Name 1	Product 15	142	575
23		Name 4	Product 2	999	828
24		Name 3	Product 26	913	778
25		Name 4	Product 3	258	63
26		Name 6	Product 17	227	783
27		Name 4	Product 3	493	767
28		Name 6	Product 2	737	548
29		Name 4	Product 19	620	391
30
31	To Search
32	Name 2
33
34	Name 2	Product 10	149	53
35	Name 2	Product 18	870	712
36	Name 2	Product 25	430	551
37	Name 2	Product 3	99	670
38
Extract

Cell Formulas
Range	Formula
A34:D38	A34	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$29)/($B$2:$B$29=$A$32),ROWS(A$34:A34))),"")

Thanks peter

 

[Peter_SSs]

Thanks peter

You're welcome.