COPY ROW BY ROW based on pattern matching

Closed
vinod - Sep 4, 2018 at 06:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 4, 2018 at 11:34 AM
i have the following data in sheet 1

A B C D E F
day Pe M1 M2 M3 M4
1 1 UCB008 P UNL002 P BML002P CML003P
1 1 CBCL CBOL ECE ECE
1 2 LAB LAB LAB 1 LAB 2
1 2 TA1 DMA TA14 SUM

i want the above data in the following format

1 1 UCB008P CBCL LAB TA1 M1 ( here 1 1 is in the same row as is UCB008 in sheet2

First search P (space P) in sheet, it will be there in C2 (UCB008 P),
Now I want to copy the UCB008 P, IN SHEET 2 WITH paste as value
Now I want to copy the CBCL IN next column in sheet 2
Similary I want to copy LAB in third column and TA1 in 4th column
and copy the header M1 IN THE 5TH COLUMN

COPY the value of A(REFERNCED ROW5), B(REFERENCED ROW NUMBER) as in this case is 1,1( or value of A1, B1).

THEN Again search the the value of P and do the same process until the entire worksheet is searched

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Sep 4, 2018 at 11:34 AM
Hi Vinod,

It is unclear to me which data needs to go where.

You probably have more rows, so why stop at the 4th row?

Would the next result row start with 1 1 or 1 2?
Find space P and then transpose the 3 value below it?

Could you clarify your query?

Best regards,
Trowa