What is the bes excel formula to use.

[iamlisa]

Hi,

I need help, what is the best excel formula should I use to get the data as a Result. Please help. Thanks.

Original Data Result Corporate Total Corporate Total COMPANY A COMPANY A 360 ABC 360.00 COMPANY B 135 360.00 COMPANY C 65 COMPANY B COMPANY D 120 CDE 30.00 ABC 75.00 FGH 30.00 135.00 COMPANY C IJK 65.00 65.00 COMPANY D LMN 60.00 IJK 60.00 120.00 <COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY> </TBODY> </SPAN> <TBODY> </TBODY><COLGROUP><COL><COL></COLGROUP>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[skywriter]

Try this:

With the data in the cells exactly as you see:

Formula in Cell E2, confirm with Control Shift Enter, not just enter and copy down.

=INDEX($B$3:$B$17,SMALL(IF(ISBLANK($B$3:$B$17)*ROW($B$3:$B$17)>0,ROW($B$3:$B$17)-ROW($B$3)+1),ROWS($E$2:E2))-1)

Excel 2010

	A	B	C	D	E
1	Corporate	Total		Corporate	Total
2	COMPANY A			COMPANY A	360
3	ABC	360		COMPANY B	135
4		360		COMPANY C	65
5	COMPANY B			COMPANY D	120
6	CDE	30
7	ABC	75
8	FGH	30
9		135
10	COMPANY C
11	IJK	65
12		65
13	COMPANY D
14	LMN	60
15	IJK	60
16		120

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Array Formulas Cell Formula E2 {=INDEX($B$3:$B$17,SMALL(IF(ISBLANK($B$3:$B$17)*ROW($B$3:$B$17)>0,ROW($B$3:$B$17)-ROW($B$3)+1),ROWS($E$2:E2))-1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[iamlisa]

Try this:

With the data in the cells exactly as you see:

Formula in Cell E2, confirm with Control Shift Enter, not just enter and copy down.

=INDEX($B$3:$B$17,SMALL(IF(ISBLANK($B$3:$B$17)*ROW($B$3:$B$17)>0,ROW($B$3:$B$17)-ROW($B$3)+1),ROWS($E$2:E2))-1)

Excel 2010

	A	B	C	D	E
1	Corporate	Total		Corporate	Total
2	COMPANY A			COMPANY A	360
3	ABC	360		COMPANY B	135
4		360		COMPANY C	65
5	COMPANY B			COMPANY D	120
6	CDE	30
7	ABC	75
8	FGH	30
9		135
10	COMPANY C
11	IJK	65
12		65
13	COMPANY D
14	LMN	60
15	IJK	60
16		120

<TBODY>
</TBODY>

Sheet9​

Array Formulas Cell Formula E2 {=INDEX($B$3:$B$17,SMALL(IF(ISBLANK($B$3:$B$17)*ROW($B$3:$B$17)>0,ROW($B$3:$B$17)-ROW($B$3)+1),ROWS($E$2:E2))-1)} <TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Thank you very much skywriter. Very Helpful. Perfect.

What formula should I use so that the Corporate Name will apprear in Cell D. Please help.

 

[Oeldere]

You want to analyze your data.
In that case I would take another approach.

a3 =

=if(B2="",B3,A2)

now you also have access to your product types (abc)

A B C

COMPANY A COMPANY A
COMPANY A ABC 360.00
COMPANY A 360.00
COMPANY B COMPANY B
COMPANY B CDE 30.00
COMPANY B ABC 75.00
COMPANY B FGH 30.00
COMPANY B 135.00
COMPANY C COMPANY C
COMPANY C IJK 65.00
COMPANY C 65.00
COMPANY D COMPANY D
COMPANY D LMN 60.00
COMPANY D IJK 60.00
COMPANY D 120.00