How to arrange two columns?

[Bilingual]

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?

 

[JackDanIce]

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

 

[Bilingual]

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

[TABLE="width: 196"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Departure[/TD]
[TD]Arrival[/TD]
[TD]Operations[/TD]
[/TR]
[TR]
[TD]HOK02[/TD]
[TD]HOK26[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]HOK04[/TD]
[TD]HOK29[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]HOK05[/TD]
[TD]HOK31[/TD]
[TD="align: right"]567[/TD]
[/TR]
[TR]
[TD]HOK06[/TD]
[TD]BJR24[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]HOK20[/TD]
[TD]BJR27[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]HOK21[/TD]
[TD]BJR28[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]HOK22[/TD]
[TD]BJR29[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]HOK23[/TD]
[TD]BJR32[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]HOK25[/TD]
[TD]BJR33[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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]

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

[TABLE="width: 567"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Departure[/TD]
[TD]Arrival[/TD]
[TD]Operations[/TD]
[TD]Total Adress[/TD]
[TD]Total number of operations[/TD]
[/TR]
[TR]
[TD]HOK02[/TD]
[TD]HOK26[/TD]
[TD="align: right"]3[/TD]
[TD]HOK02[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]HOK04[/TD]
[TD]HOK29[/TD]
[TD="align: right"]5[/TD]
[TD]HOK04[/TD]
[TD="align: right"]103[/TD]
[/TR]
[TR]
[TD]HOK05[/TD]
[TD]HOK31[/TD]
[TD="align: right"]567[/TD]
[TD]HOK05[/TD]
[TD="align: right"]567[/TD]
[/TR]
[TR]
[TD]HOK06[/TD]
[TD]BJR24[/TD]
[TD="align: right"]7[/TD]
[TD]HOK06[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]HOK20[/TD]
[TD]HOK04[/TD]
[TD="align: right"]98[/TD]
[TD]HOK20[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]HOK21[/TD]
[TD]BJR28[/TD]
[TD="align: right"]7[/TD]
[TD]HOK21[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]HOK22[/TD]
[TD]BJR29[/TD]
[TD="align: right"]6[/TD]
[TD]HOK22[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]HOK23[/TD]
[TD]BJR32[/TD]
[TD="align: right"]6[/TD]
[TD]HOK23[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]HOK25[/TD]
[TD]HOK02[/TD]
[TD="align: right"]5[/TD]
[TD]HOK25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]HOK26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]HOK29[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]HOK31[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BJR24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]HOK04[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BJR28[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BJR29[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BJR32[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]HOK02[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

... , 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
	A	B	C	D	E
1	Departure	Arrival	Operations	Address	Total
2	HOK02	HOK26	3	HOK02	8
3	HOK04	HOK29	5	HOK04	103
4	HOK05	HOK31	567	HOK05	567
5	HOK06	BJR24	7	HOK06	7
6	HOK20	HOK04	98	HOK20	98
7	HOK21	BJR28	7	HOK21	7
8	HOK22	BJR29	6	HOK22	6
9	HOK23	BJR32	6	HOK23	6
10	HOK25	HOK02	5	HOK25	5
11				HOK26	3
12				HOK29	5
13				HOK31	567
14				BJR24	7
15				BJR28	7
16				BJR29	6
17				BJR32	6
18
Sheet6

 

[Bilingual]

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

	A	B	C	D	E
Departure	Arrival	Address
HOK02	HOK26	HOK02
HOK04	HOK29	HOK04
HOK05	HOK31	HOK05
HOK06	BJR24	HOK06
HOK20	HOK04	HOK20
HOK21	BJR28	HOK21
HOK22	BJR29	HOK22
HOK23	BJR32	HOK23
HOK25	HOK02	HOK25
			HOK26
			HOK29
			HOK31
			BJR24
			BJR28
			BJR29
			BJR32

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]3[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]4[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]7[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]11[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]

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

[TD="bgcolor: #cacaca, align: center"]13[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]

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

[TD="bgcolor: #cacaca, align: center"]15[/TD]

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

[TD="bgcolor: #cacaca, align: center"]16[/TD]

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

[TD="bgcolor: #cacaca, align: center"]17[/TD]

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

[TD="bgcolor: #cacaca, align: center"]18[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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]

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.