How to arrange two columns?

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
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?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
... , 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
 

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,691
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,555
Messages
5,548,727
Members
410,867
Latest member
Dhanas
Top