How to arrange two columns?

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
186
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, i have a simple data sheet with a difficult problem.

I have three columns which contains of following:

1. Departure adress
2. Arrival adress
3. Number of operations

The adress can be both departure and arrival.

What i want is to group departure and arrival adress, so i can get the total number of operations from any particular adress.

How to do that?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
A SUMPRODUCT formula can probably answer this for you, have a read of:
Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online

Otherwise, include a screenshot of your spreadsheet before and after indicating what you are after
DepartureArrivalOperations
HOK02HOK263
HOK04HOK295
HOK05HOK31567
HOK06BJR247
HOK20BJR2798
HOK21BJR287
HOK22BJR296
HOK23BJR326
HOK25BJR335

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You said that an address can be both departure and arrival, but your sample does not appear to include any examples like that.

Also, Jack asked for before and after screen shots as that would help clarify what you wanted.
 
Upvote 0
You said that an address can be both departure and arrival, but your sample does not appear to include any examples like that.

Also, Jack asked for before and after screen shots as that would help clarify what you wanted.

Hi again, the sample was just an example, but i have altered the sample so you can see what i would like to end up with

DepartureArrivalOperationsTotal AdressTotal number of operations
HOK02HOK263HOK028
HOK04HOK295HOK04103
HOK05HOK31567HOK05567
HOK06BJR247HOK067
HOK20HOK0498HOK2098
HOK21BJR287HOK217
HOK22BJR296HOK226
HOK23BJR326HOK236
HOK25HOK025HOK255
HOK26
HOK29
HOK31
BJR24
HOK04
BJR28
BJR29
BJR32
HOK02

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
... , but i have altered the sample so you can see what i would like to end up with
That's what we need. ;)

Would this be sufficient?
Formula copied down (after adjusting ranges to suit where your data really is)

Excel Workbook
ABCDE
1DepartureArrivalOperationsAddressTotal
2HOK02HOK263HOK028
3HOK04HOK295HOK04103
4HOK05HOK31567HOK05567
5HOK06BJR247HOK067
6HOK20HOK0498HOK2098
7HOK21BJR287HOK217
8HOK22BJR296HOK226
9HOK23BJR326HOK236
10HOK25HOK025HOK255
11HOK263
12HOK295
13HOK31567
14BJR247
15BJR287
16BJR296
17BJR326
18
Sheet6
 
Upvote 0
Thanks a lot, you are a genius :)

One Question, as the list of arrival and departure addresses will vary from month to month, could i generate the "total adress" automatically in some way?

That's what we need. ;)

Would this be sufficient?
Formula copied down (after adjusting ranges to suit where your data really is)

Sheet6

ABCDE
1DepartureArrivalOperationsAddressTotal
2HOK02HOK263HOK028
3HOK04HOK295HOK04103
4HOK05HOK31567HOK05567
5HOK06BJR247HOK067
6HOK20HOK0498HOK2098
7HOK21BJR287HOK217
8HOK22BJR296HOK226
9HOK23BJR326HOK236
10HOK25HOK025HOK255
11 HOK263
12 HOK295
13 HOK31567
14 BJR247
15 BJR287
16 BJR296
17 BJR326
18

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:106px;"><col style="width:89px;"><col style="width:88px;"><col style="width:87px;"><col style="width:69px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E2=SUMIF(A$2:A$10,D2,C$2:C$10)+SUMIF(B$2:B$10,D2,C$2:C$10)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Was out for lunch but yes I was confused with there being no before and after screenshots!

Bilingual - do an online search for using Advanced data filter to find unique records. It's a good thing to learn for yourself and will answer the above question
 
Upvote 0
Unless you are going to copy the items from columns A & B into a single column somewhere first, I'm not sure that Advanced Filter will get you the unique list. In any case that really isn't 'automatic'.


See if this thread helps.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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
Back
Top