lookup multiple rows vertically and paste hotizontally on another sheet

[Rodrigues]

Hi there

I’m would like to know if there is a formula to accomplish that, lookup vertically, copy and paste values/texts horizontally on sheet2.

Look at Product code in B3 and description in B6 and all the texts rows B13:B20 then paste product code values, description and the texts fields without the first 4 digits onto Sheet2 starting on A2, and so on, as per example on Sheet2.

I know how to copy and paste (one by one) the problem is that, have a few hundreds products entries to copy and was looking for a fastest way to do it.

Ref texts are always 8 rows each, highlighted in yellow are the fields I need to look and extract data.

Hope this question is in the correct place.
Thanks in advance.
R

 

[odearja]

I have no doubt someone in this forum could provide a more efficient method, but this is what I would do for a right now solution:

Create a list so I could sort by color in col B (removing blanks and unwanted data).
Copy/Paste Special Transpose where you want the data (this will flip your vertical to horizontal).

Select the text data that you want to eliminate the first 4 and use a function like

 =RIGHT(B13,LEN(B13)-4)

to strip out the digits you don't want (B13 is the cell with the source value). Again, Copy/Paste Special if necessary.

Again, there might be better ways of doing this task, but using this methodology would save a lot of time as opposed to copy and pasting one by one. If your format lends itself to the job, consider making a macro to do this for you.

 

[Rodrigues]

Thanks for your reply Odearja much appreciated.