Consolidating data from columns to rows

debugcn Published at Dev

Oliver Yasuna

I have a spreadsheet:

Company 1 | ID1
Company 1 | ID2
Company 1 | ID3
Company 2 | ID4
Company 2 | ID5
Company 2 | ID6
Company 3 | ID7
Company 3 | ID8
Company 3 | ID9

I need to combine each company into one row, with all their IDs into columns to the right. There can be a different number of IDs per company.

The final result would look like:

Company 1 | ID1 | ID2 | ID3
Company 2 | ID4 | ID5 | ID6
Company 3 | ID7 | ID8 | ID9

How could I accomplish this? Preferably with VBA.

robinCTS

This single formula solution assumes that each company has the same number of rows:

Enter the following formula in D2 and ctrl-enter/copy-paste/fill-down&right into the rest of the table:

=""&INDEX($A:$B,ROW($D$2)+3*INT(ROW()-ROW($D$2))+MAX(0,COLUMN()-COLUMN($D$2)-1),2-(COLUMN()=COLUMN($D$2)))

Note that this formula will work for any (identical) number of rows for each company, and will correctly adjust if rows or columns are added/removed.

However, the formula will break if the table it is in is copied/cut and pasted elsewhere. The $D$2s need to be changed to match the new table location's top-left data cell in order to make it work correctly again.

If there is a variable number of rows for each company, the single formula required is:

=IFERROR(IF(COLUMN()=COLUMN($D$2),""&INDEX($A:$A,MATCH(D1,$A:$A,0)+COUNTIF($A:$A,D1)),INDEX(INDEX($B:$B,MATCH($D2,$A:$A,0)):INDEX($B:$B,MATCH($D2,$A:$A,0)+COUNTIF($A:$A,$D2)-1),COLUMN()-COLUMN($D$2))),"")

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-11

Comments

0 comments

From Dev

Related Related

Article

Consolidating data from columns to rows

Consolidating data from columns to rows

Update SQL data, consolidating columns

move data from rows to columns

Pandas: consolidating columns in DataFrame

Copying & Consolidating Data from on-prem SQL server to Azure blob

Consolidating array data

copy specific columns from rows with duplicate data

SQL Results data from rows to columns

Excel - copy Data from multiple columns and rows

R transforming data from columns to rows by variable

Aggregating data from a combination of selected rows and columns

Consolidating duplicate rows in a large dataframe

INSERT INTO after CONCAT data from different columns from different rows

Consolidating columns based on field value in bash

Consolidating multiple columns into one column in R

Consolidating columns based on field value in bash

consolidating multiple columns into one column pandas

Removal of Duplicate Rows from Data table Based on Multiple columns

Writing Columns of data versus Rows in Excel from XML with XSLT

Deleting a specific rows and columns from a data-set using Matlab

How to exclude rows/columns from numpy.ndarray data

Remove "duplicated" rows from data frame (they differ in few columns)

SQL or MSEXCEL transpose data from rows to columns NOT PIVOT nor TRANSFORM

Remove NAs from data frame without deleting entire rows/columns

Event data from rows to columns by ID in R, Reshape?

Pandas Create Columns From Rows in Other Data Frame with Criteria

Remove rows from a data frame that contain duplicate information across the columns

R: aggregation (median) of data.frame rows from >2 columns

Excel VBA code to transpose data from rows to columns

How to move Excel 2010 data from rows into 2 columns only?