COPY ROW BY ROW based on pattern matching [Closed]

Report
-
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
-
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 reply

Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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