Help for Look Up reference

[KIRAN VARMA]

Hi, I am looking to produce sequence for a group of data as given below, I have tried and tested every other reference function but still not find my way.

As per the given below I need sequence in coloumn A

<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272 x:str><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" span=2 width=136><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 102pt; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 width=136 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 102pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 width=136>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=19 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 37.5pt; mso-height-source: userset" height=50><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 37.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=50 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>ABC</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>XYZ</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=21 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103 height=32 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl103>CYX</TD></TR></TBODY></TABLE>
Since my data runs in thousands of rows, it is beyond me to do this sequencing manually and currently I am trying following function

=OFFSET(B2,ROW()-ROW(B1:B10)/10,0)*10

 

[asking]

Hi KIRAN,

I presume you have data in Column A and you wish to put in the next 9 cells whatever is in cell A1. On reaching cell A11 you wish to put the data in cell A11 for the next 10 cells and so forth... If that is the correct assumption you could try this ~ Select all of column A and insert a column, in the first cell which is now cell A1 type whatever is in cell B1.

Next step is enter this formula in cell A2 and fill down for the length of your data
=IF(RIGHT(ROW(),1)="1",B2,A1)

When you have done that select all of Column A, copy and select Cell B1, and paste special values only. Now you can delete Column A.

Always save your workbook before trying something new so you can revert if anything goes wrong.

If your data does not start in row 1 change the "1" in the formula to whatever row number your data starts in.
Cheers