What is the quick way to transfer data from row to column

[Sian1]

Request ID	101	102	104	105	116	134	135	143	147	148	149
R-042A04GC700000											149
R-023A0DAB800000											149
R-042A059Q100000								143			149
R-042A059Q100000
R-042A059Q100000
R-005A0HFX800001		102		105							149
R-042A059Y400000			104			134					149
R-042A059Y400000											149
R-023A0BE0200000											149
R-001A0Y3T700000						134	135
R-001A0Y3T700000					116			143	147		149
R-023A0BE0200000	101

 

[Sian1]

It is different code inside the query. Copy and paste that and try.

K thank

 

[Sian1]

It is different code inside the query. Copy and paste that and try.

Hi
I swear it works in the workbook you sent back but it didn't work when i did it exact in the same link. I am trying to convert the last 5 worksheets

Box

 

[MARK858]

It wouldn't convert those 5 sheets correctly as your layout is different to your test sample.
I'll see if I get time to look at it tomorrow.

 

[Sian1]

It wouldn't convert those 5 sheets correctly as your layout is different to your test sample.
I'll see if I get time to look at it tomorrow.

Thanks so much.

 

[Peter_SSs]

is it possible to do it in VBA?

If you are still interested in that approach, you could try this with a copy of your workbook.

Sub ReworkCodes()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  With Range("Table1")
    a = .Value
    uba2 = UBound(a, 2)
    ReDim b(1 To UBound(a, 1) * uba2, 1 To 2)
    For i = 1 To UBound(a)
      For j = 2 To uba2
        If Len(a(i, j)) Then
          k = k + 1
          b(k, 1) = a(i, 1): b(k, 2) = a(i, j)
        End If
      Next j
    Next i
    With .Offset(, .Columns.Count + 2).Resize(, 2)
      .Rows(0).Value = Array("Request ID", "Rework Code")
      .Resize(k).Value = b
    End With
  End With
End Sub

 

[MARK858]

Hi Peter, just been alerted to your post. Just so you are aware the latest layout in the workbook is as per the below (obviously for more rows )...
Edited as didn't include column A on the previous paste

NP Reject (1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	Request ID	NPP Rejection Date - History	NPP Rejection Reason - History	NPP Rejection Comments - History	Request Rejection Count	101	102	104	105	106	107	109	112	114	115	116	117	118	119	120	122	124	126	127	129	130	131	132	133	134	135	136	137	138	139	140	141	142	143	144	145	146	147	148	149
2	R-042A04GC700000	29-Dec-2017	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS;		1																																								149
3	R-023A0DAB800000	08-Jan-2019	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS;	Please, correct the Legal entity on RI invoice to American International Group UK Limited (AIG UK).Make the RI invoice visible for the Partner. Upload it as premium bill/premium bill	1																																								149
4	R-042A059Q100000	20-Sep-2018	143 NETWORK PARTNER FORM INNACCURATE / NOT ATTACHED; 149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS; 129 DISCREPANCY/MISSING FACULTATIVE DECLARATION PAGE;	En el recibo y en el certificado todavía pone Excess. Hay una dirección de Mozambique en el NPPRF Error en el certificado	1																				129														143						149
5	R-042A059Q100000	01-Oct-2018	129 DISCREPANCY/MISSING FACULTATIVE DECLARATION PAGE;	Por favor modificar el certificado – hay un error en la tabla de impuestos y también la dirección del Asegurado está en Mozambique	1																				129
6	R-042A059Q100000	03-Oct-2018	129 DISCREPANCY/MISSING FACULTATIVE DECLARATION PAGE;	Por favor modificar el certificado – hay un error en la tabla de impuestos y también la dirección del Asegurado está en Mozambique	1																				129
7	R-005A0HFX800001	30-Jan-2020	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS; 105 DISCREPANCY WITH PREMIUM VALUES; 102 DISCREPANCY WITH POLICY INCEPTION/EXPIRATION DATES;	Pls, amend effective date to Feb. 1st in both Omega and the RI invoice (due to backdating restrictions in Chile). Pls, add NP fee and amend NEP in Omega. Pls, amend Omega Ref. number in the RI invoice.	1		102		105																																				149
8	R-042A059Y400000	28-Aug-2018	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS; 134 DISCREPANCY WITH INSURER TAXES; 104 COVERAGES INDICATED NOT PROVIDED LOCALLY;	Please remove the EL, a special company covers it for Uruguay; correct the insurer tax to USD 12.27; correct the net exportable to 478.63; provide RI	1			104																						134															149
9	R-042A059Y400000	29-Aug-2018	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS;	Please amend the RI to reflect the correct local name insured, address and contact name	1																																								149
10	R-023A0BE0200000	22-Dec-2017	149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS;		1																																								149
11	R-001A0Y3T700000	11-Oct-2017	134 DISCREPANCY WITH INSURER TAXES; 135 DISCREPANCY WITH INSURED TAXES;	Premium to be clarified, policy already issued	1																									134	135
12	R-023A0BE0200000	26-Apr-2018	101 DISCREPANCY WITH LOCAL INSURED DETAILS;		1	101
13	R-001A0Y3T700000	22-Jan-2018	143 NETWORK PARTNER FORM INNACCURATE / NOT ATTACHED; 147 MISSING AUTO LIST OF VEHICLES; 149 INCORRECT / MISSING INVOICE OR REQUIRED DOCUMENTS; 129 DISCREPANCY/MISSING FACULTATIVE DECLARATION PAGE; 116 DISCREPANCY WITH ISSUING OFFICE OVERRIDE/FEES;	1. NP fee should be $1139,30 2. Net exp premium should be $9035 3. Please attach the SOV 4. Please amend accordingly the NPPRF, RI invoice and Fac cert Please resubmit	1											116									129														143				147		149
NPP History

 

[Peter_SSs]

Just so you are aware the latest layout in the workbook is ...

Thanks Mark. I'll wait to see if the OP is interested in this approach based on the original sample data. If so, perhaps they will also provide an appropriate sample results for that new layout if not able to amend the code themselves.

 

[MARK858]

I have added queries to 4 of the 5 tabs.

Please note that the points raised in the quote below are incorrect, the output in KL is correct. I have left the quote in so the posts afterwards still make sense.

KL tab I have issues with as you have 101 in the cells rather than the column header value. Both my queries and the code Peter posted use the data in the cells so we get 101 for all the results (what is in KL sheet is the result of Peter's code adjusted for the sheet layout).

Peter's code can probably be adjusted to return the header value in this circumstance but I don't know how to do it with a query (we might get a response from @lrobbo314 or @sandy666 saying there is a way, just have to wait and see).

Sample code from the Table1 query (they all change slightly as there are different columns/No of columns in each tab)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"NPP Reject Date", "NPP Reject Reason", "NPP Reject Comments", "Request Rejection Count"}),
    Unpivot = Table.UnpivotOtherColumns(#"Removed Columns", {"Request ID"}, "Attribute", "Value"),
    Remove = Table.RemoveColumns(Unpivot,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(Remove, each ([Value] <> ""))
in
    #"Filtered Rows"

Link to workbook

 

[lrobbo314]

Mark, I'm lost at this point. What are we trying to do now? Are we just looking at sheet "KL"? What is the desired output?

 

[MARK858]

I understand your confusion, just looked again at the table and the result should be a whole series of 101's. I mistakenly assumed that it was like some of the tables in the earlier workbook which had a layout like...

It doesn't, so all good I hope.

I have put an edit in my previous post.