Transpose based on key

[MattPM]

Hi,

Trying to organise a spreadsheet so all the items with the same key are in the same row. Currently its all in just 2 columns like below:


Key Part No
1 F124
1 FR124
1 FLW124
1 FS124
2 F2941
2 DW2941


Instead I want it to look like the below:

1 F124 FR124 FLW124 FS124
2 F294 DW2941

Is there an easy way of applying this to large amounts of data?

Thanks.

[oeldere]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

What determines the key (is the the value 1.... then 2.... etc)?

[Special-K]

Input in Sheet1
Output in Sheet2
Assumes input table is only two columns

Please Login or Register  to view this content.

[alansidman]

Try this VBA solution

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[AlKey]

Here is a formula solution
Enter formula in D2 and copy across until you see blanks and then down.

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G
1	Key	Part No	Key
2	1	F124	1	F124	FR124	FLW124	FS124
3	1	FR124	2	F2941	DW2941
4	1	FLW124
5	1	FS124
6	2	F2941
7	2	DW2941

[Pete_UK]

Assuming your data is in columns A and B, you can put this formula in C2:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

Copy this down as far as you like, to accommodate new data being added. The hyphens will show where the formula is active.

List your individual keys in column E, starting with E2, then put this formula in F2:

=IFERROR(INDEX($B:$B,MATCH($E2&"_"&COLUMNS($F:F),$C:$C,0)),"")

Copy this across and down, as required.

The attached file illustrates this.

Hope this helps.

Pete

[Tony Valko]

Is there an easy way of applying this to large amounts of data?

How much is a "large amount of data"?

[crj54]

Try this, assumes that Row 1 is header row and data starts in cell A2

Please Login or Register  to view this content.

This will work fast for large data sets (even if in excess of 1,000,000 rows of data)