Excel Function - Which one?

  • Thread starter Thread starter sassykeon
  • Start date Start date
S

sassykeon

Hi,

I was hoping to receive help on doing the following:

I have a spreadsheet with the first column that has titles of
books.
I want to create a second column that pulls the title of
the book in row 2 of column 1 and print that title into
the first 9 rows of column 2. Then, I want to pull the title of
the row 10 of column 1 and print that title into the second 9
rows (rows 10 to 18) of column 2, and so on.

It would look like this originally:

Column 1 | Column 2
Title 1
Title 2
Title 3
Title 4
Title 5
Title 6
Title 7
Title 8
Title 9
Title 10
Title 11
Title 12
Title 13
Title 14
Title 15
Title 16
Title 17
Title 18
Title 19
Title 20


But i want this to happen:

Column 1 | Column 2
Title 1 | Title 1
Title 2 | Title 1
Title 3 | Title 1
Title 4 | Title 1
Title 5 | Title 1
Title 6 | Title 1
Title 7 | Title 1
Title 8 | Title 1
Title 9 | Title 1
Title 10 | Title 10
Title 11 | Title 10
Title 12 | Title 10
Title 13 | Title 10
Title 14 | Title 10
Title 15 | Title 10
Title 16 | Title 10
Title 17 | Title 10
Title 18 | Title 10
Title 19 | Title 19
Title 20 | Title 19
and so on...


Can you please show me the exact formula to insert into
the first row of column 2 to do this
 
Interesting question

may i ask for the purpose of this?. Assuming that your title list
starts in row 2 enter the following formula in B2
=INDIRECT("A" & INT((ROW()-2)/9)+2)
copy this formula for the other rows

HTH
Frank
 
The OPs question seems a little confused as to whether the data starts in
row 2 or row 1. If it is row 2, Frank's formula works. If it is row, it
should be amended to

=INDIRECT("A" &INT((ROW(A1)-1)/9)*9+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Frank and Bob,

Thanks for posting your replies. The data starts on
the 2nd row, but Bob, your formula didn't work out perfectly.
After taking the title form row 2 of column 1 and printing
it in the first 9 rows of column 2, it took the title from
row 3 of column 1 and printed it in the next 9 rows of
column 2, when it should have taken the title from
row 10 of column 1, and printed that. I appreciate your
help though.

Frank, your formula works perfectly, but I just had to adjust
the row 2/row 1 confusion.

Btw, I'm doing this to organize a datafeed file that contains a list o
book products that I'm going to use to make a website
 
Back
Top