which formula?

[hldemirkaya]

SOURCE TABLE
TURKEY ANKARA
TURKEY ISTANBUL
TURKEY IZMIR
USA NEW YORK
USA WASHINGTON

which formula can i use to obtain a table as below?

TURKEY ANKARA - ISTANBUL - IZMIR
USA NEW YORK - WASHINGTON

ANOTHER SAMPLE:
SOURCE TABLE
A 1
A 16
A 45
B 12
B 80

TARGET TABLE
A 1 - 16 - 45
B 12 -80

Thank you very much for your attentions.

 

[javalover]

Aladin, I posted this yesterday: I have a directory of company names with multiple contacts. Currently it has one row per contact. Each company has a different amount of contacts. I need there to be only ONE row per company name, moving the contacts up to multiple columns.

Currently:
ABC Company, John Smith, President
ABC Company, Ted Wilson, VP
ABC Company, William Nelson, IT
NBC Company, Scott Jones, CEO
NBC Company, Jeff Anderson, CFO

Needs to be:
ABC Company, John Smith, President, Ted Wilson, VP, William Nelson, IT
NBC Company, Scott Jones, CEO, Jeff Anderson, CFO

Note: "John Smith, President" is in one cell (as are the other contact names & titles). The goal is to have them in a heir own column (i.e. like your example...columns G, H, I..)

thanks for your help!

 

[Aladin Akyurek]

Aladin, I posted this yesterday: I have a directory of company names with multiple contacts. Currently it has one row per contact. Each company has a different amount of contacts. I need there to be only ONE row per company name, moving the contacts up to multiple columns.

Currently:
ABC Company, John Smith, President
ABC Company, Ted Wilson, VP
ABC Company, William Nelson, IT
NBC Company, Scott Jones, CEO
NBC Company, Jeff Anderson, CFO

Needs to be:
ABC Company, John Smith, President, Ted Wilson, VP, William Nelson, IT
NBC Company, Scott Jones, CEO, Jeff Anderson, CFO

Note: "John Smith, President" is in one cell (as are the other contact names & titles). The goal is to have them in a heir own column (i.e. like your example...columns G, H, I..)

thanks for your help!

Are you on Excel 2007 or later?

 

[T. Valko]

I am Facing Same Problem

Example

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	name	Location				Expected Result
2	Hardeep	Delhi				Hardeep	Delhi	Noida	Gurgaon
3	Hardeep	Noida				Pardeep	Kanpur	Varanasi
4	Hardeep	Gurgaon				Neeraj	Bangalore	Chennai
5	Pardeep	Kanpur
6	Pardeep	Varanasi
7	Neeraj	Bangalore
8	Neeraj	Chennai
Sheet1

Excel 2007



Regards

Try one of these...

If the data really is grouped together as is shown in your posted sample...Sheet1<table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Name</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Location</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Hardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Delhi</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Hardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Noida</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Hardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Gurgaon</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Pardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Kanpur</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Pardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Varanasi</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Neeraj</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Bangalore</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Neeraj</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">ChennaiSheet1<table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /><col style="width:72px;" /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Name</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Count</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Location</td><td style="color:#ffffff; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Hardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Delhi</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Noida</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Gurgaon</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Pardeep</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Kanpur</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Varanasi</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Neeraj</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Bangalore</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Chennai</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_
Enter this formula in E2. This will return the count of records for each name.

=COUNTIF(A:A,D2)

Copy down to E4.

Enter this formula in F2. This will return the locations for each name.

=IF(COLUMNS($F2:F2)>$E2,"",INDEX($B:$B,MATCH($D2,$A:$A,0)+COLUMNS($F2:F2)-1))

Copy down to F4 then across until you get a solid column of blanks.

 

[T. Valko]

If the data is not grouped and is random...

Book1
	A	B
1	Name	Location
2	Hardeep	Noida
3	Neeraj	Chennai
4	Pardeep	Varanasi
5	Hardeep	Delhi
6	Pardeep	Kanpur
7	Neeraj	Bangalore
8	Hardeep	Gurgaon
Sheet1

For this version I use the following defined names in the formulas:

• Names
• Refers to: =$A$2:$A$8
• Location
• Refers to: =$B:$B

Enter this formula in E2. This will return the count of records for each name.

=COUNTIF(Names,D2)

Copy down to E4.

Enter this array formula** in F2. This will return the locations for each name.

=IF(COLUMNS($F2:F2)>$E2,"",INDEX(Location,SMALL(IF(Names=$D2,ROW(Names)),COLUMNS($F2:F2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down to F4 then across until you get a solid column of blanks.

 

[javalover]

thanks, Biff...worked beautifully!

KUDOS!!

 

[T. Valko]

thanks, Biff...worked beautifully!

KUDOS!!

You're welcome. Thanks for the feedback!