# What is the bes excel formula to use.

#### iamlisa

##### New Member
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>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
Code:
``=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
ABCDE
1CorporateTotalCorporateTotal
2COMPANY ACOMPANY A360
3ABC360COMPANY B135
4360COMPANY C65
5COMPANY BCOMPANY D120
6CDE30
7ABC75
8FGH30
9135
10COMPANY C
11IJK65
1265
13COMPANY D
14LMN60
15IJK60
16120

</tbody>
Sheet9

Array Formulas
CellFormula
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>
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>

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.
Code:
``=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.

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

a3 =
Code:
``=if(B2="",B3,A2)``

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

Replies
0
Views
1K
Replies
3
Views
1K
Replies
1
Views
2K
Replies
4
Views
665
Replies
1
Views
931

1,218,752
Messages
6,144,285
Members
450,535
Latest member
Erlull

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back