Transpose one of two columns only

**Lana74** · 10-22-2014, 12:42 PM

Hi,

The data I have now is in following format:

Company Committee
Microsoft Audit
Micorsoft Remuneration
IBM Nominating
IBM Finance
IBM 401K
Zynga Compensation

This is just a sample. Each company has from 1 to possibly 5 committees.

What I need is for the file to look as following:

Company Committee1 Committee2 Committee3
Micorsoft Audit Remuneration
IBM Nominating Finance 401K
Zynga Compensation

So basically, one row per company, with committees in separate columns.

Thank you very much in advance!

**misrasomendra** · 10-22-2014, 01:12 PM

Hi,

See below file, yellow cells has solution. Note the formula's in the cells are array formula, which must be confirmed with Ctrl+Shift+Enter.

Edit: Also note to get Unique company name I had use Remove duplicate option from Data Tab to reduce calculation load for a bigger data set, but if you required that can also be done through formulas.

**canapone** · 10-22-2014, 01:21 PM

Hi,

an example: in A2:A10 Companies in B2:B10 Commitees

try to copy in D2: after copied the formula you have to confirm it with control+shift+enter (see on Google Excel array formulas)

excel 2003 formulas

=INDEX($A$2:$A$10,SMALL(IF(ISNUMBER(MATCH(ROW($A$2:$A$10)-1,MATCH($A$2:$A$10,$A$2:$A$10,0),0)),MATCH($A$2:$A$10,$A$2:$A$10,0),""),ROWS(A$1:A1)))

and copy down the formula to get the list in D2:D?

----------------------

in E2 to be confirmed with control+shift+ enter and copied to the right

=INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$C2,ROW($A$2:$A$10)-1),COLUMNS($A$1:A$1)))

Hope it helps

**newdoverman** · 10-22-2014, 02:05 PM

This might suit your needs.

To get the company names:
Array enter in E2 and copy down. (array to be in E1 to H4)

Formula:

Please Login or Register  to view this content.

To get the committee values:
Array enter in F2 and copy across and down:

Formula:

Please Login or Register  to view this content.

**Lana74** · 10-24-2014, 10:00 AM

Thanks guys! This worked great....for a small sample I provided. However, for the life of me I couldnt understand how to make it work for a larger list.
For example, let's say there are 8 rows of companies and committees, and not 7:

=IFERROR(INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$E2,ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1),COLUMNS($A$1:A1))),"")

I tried changing "7"s in the above to "8" but it just wouldnt work. What am I missing?

Thanks again!!!

**newdoverman** · 10-24-2014, 11:23 AM

Here are the two formulae that are used in my solution expanded to accommodate to row 2000. These formulae are entered with Ctrl + Shift + Enter (array formula)

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

**Lana74** · 10-24-2014, 11:43 AM

Thank you so much!!!

**newdoverman** · 10-24-2014, 12:07 PM

You're welcome.

If you want to change the range for your data, change the "2000" value wherever it is in both formulae and enter with Ctrl + Shift + Enter.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Transpose one of two columns only

LinkBack

Thread Tools

Rate This Thread

Display

Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Re: Transpose one of two columns only

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macros to transpose from multiple columns to selected columns and maintaining cell format

[SOLVED] Transpose 2 Columns into Multiple Columns per Range of Cells

MAcro to - copy, transpose, text to columns, remove column A, combine all columns in 1

Transpose Columns To Rows (with Variable Columns)

transpose row to 2 columns

Bookmarks

Bookmarks

Posting Permissions