What is the bes excel formula to use.

iamlisa

New Member
Joined
Sep 8, 2014
Messages
3
Hi,

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

Original DataResult
CorporateTotalCorporateTotal
COMPANY ACOMPANY A360
ABC360.00COMPANY B135
360.00COMPANY C65
COMPANY BCOMPANY D120
CDE30.00
ABC75.00
FGH30.00
135.00
COMPANY C
IJK65.00
65.00
COMPANY D
LMN60.00
IJK60.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>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)}

<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

New Member
Joined
Sep 8, 2014
Messages
3
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.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
You want to analyze your data.
In that case I would take another approach.

a3 =
Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top