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.

[TABLE="width: 179"]
<TBODY>[TR]
[TD][TABLE="width: 179"]
<TBODY>[TR]
[TD][TABLE="width: 321"]
<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>[TR]
[TD="class: xl73, width: 100, bgcolor: transparent"]Original Data[/TD]
[TD="class: xl73, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 90, bgcolor: transparent"]Result[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #ff9900"]Corporate[/TD]
[TD="class: xl67, bgcolor: #ff9900"]Total[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: #ff9900"]Corporate[/TD]
[TD="class: xl67, bgcolor: #ff9900"]Total[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]COMPANY A[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]COMPANY A[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]360[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]ABC[/TD]
[TD="class: xl71, bgcolor: transparent"]360.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]COMPANY B[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]135[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]360.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]COMPANY C[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]65[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]COMPANY B[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]COMPANY D[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]CDE[/TD]
[TD="class: xl71, bgcolor: transparent"]30.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]ABC[/TD]
[TD="class: xl71, bgcolor: transparent"]75.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]FGH[/TD]
[TD="class: xl71, bgcolor: transparent"]30.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]135.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]COMPANY C[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]IJK[/TD]
[TD="class: xl71, bgcolor: transparent"]65.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]65.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]COMPANY D[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]LMN[/TD]
[TD="class: xl71, bgcolor: transparent"]60.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]IJK[/TD]
[TD="class: xl71, bgcolor: transparent"]60.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]120.00[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[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
COMPANY A	COMPANY A
ABC	COMPANY B
COMPANY C
COMPANY B	COMPANY D
CDE
ABC
FGH
COMPANY C
IJK
COMPANY D
LMN
IJK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FF9900"]Corporate[/TD]
[TD="bgcolor: #FF9900"]Total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF9900"]Corporate[/TD]
[TD="bgcolor: #FF9900"]Total[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]360[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]360[/TD]
[TD="align: right"][/TD]

[TD="align: right"]135[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]360[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=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)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
2	COMPANY A	COMPANY A
3	ABC	COMPANY B
4	COMPANY C
5	COMPANY B	COMPANY D
6	CDE
7	ABC
8	FGH
9
10	COMPANY C
11	IJK
12
13	COMPANY D
14	LMN
15	IJK
16

<TBODY>
[TD="bgcolor: #ff9900"]Corporate
[/TD]
[TD="bgcolor: #ff9900"]Total
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ff9900"]Corporate
[/TD]
[TD="bgcolor: #ff9900"]Total
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]360
[/TD]

[TD="align: right"]360
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]135
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]360
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120
[/TD]

[TD="align: right"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]75
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]135
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]65
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]60
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]60
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]120
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet9​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas
[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]E2
[/TH]
[TD="align: left"]{=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)}
[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]

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