Is this really not possible??

[simon]

Hi, I have been struggling with what seems like a simple problem to solve for
over two months and two very generous members of this forum have tried their
best to offer solutions but it's still not working.

Can anyone solve this...

I have a workbook with two sheets. Sheet 1 lists two columns of keyword
lists in columns A and B. These lists start at cells A10 and B10 with the
list heading titles and the first list itemts in cells A11 and B11. The lists
then continue down for a varying number of cells. This list is automatically
generated from the following formula using entries from the same sheet and
named lists...

=IF(ROWS(A$11:A11)<=SUM(COUNTIF(RootStatus,Rating)),INDEX(Root,SMALL(IF(RootStatus=Rating,ROW(Root)),ROWS(A$11:A11))-MIN(ROW(Root))+1),"")

The result I need is to copy the keywords from one list in Sheet 1, say in
column A11 down, to a row of 30 cells (B15:AE15) in Sheet 2. I also need the
copied list to be repeated if there are less than 30 keywords in the original
list from Sheet 1.

The best formula suggestion I have been given so far is this...

=OFFSET('Sheet 1'!$A$11,((MOD(COLUMN(),(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet 1'!$A$11:$A$200))))=0)*(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet
1'!$A$11:$A$200)))+(MOD(COLUMN(),(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet 1'!$A$11:$A$200))))))-1,0)

This formula works fine but it misses the first keyword in the list at cell
A11. The first keyword is included when the list is repeated, just not at
the start. Also, if I try to extend the range of cells in the list column
from A$11:$A$20 to anything above A$11:$A$35 I get DIV and REF errors.

Any ideas on how to solve this would be grately appreciated to say the least.

Many thanks

 

[Sheeloo]

Try
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,COUNTA(Sheet1!$A$11:$A$40))=0,COUNTA(Sheet1!$A$11:$A$40),MOD(COLUMN()-1,COUNTA(Sheet1!$A$11:$A$40))),0)

Change 40 to 10+ (number of maximum keywords in your list, assumed to be 30
in the above formula)

It will repeat from the beginning after the non-blank entries... there
should not be any blanks between entries.

 

[simon]

Hi, thanks for your response. I tried the formula but I get a #value error
message. If I change the first cell reference from $A$11 to $A$10 it displays
the list heading but thats as near as I could get.

Any ideas?

Tahnks

 

[Sheeloo]

You may download the test file I used to see what you are doing different...
Here is the download link valid for next seven days;
http://wikisend.com/download/534452/RepeatWords.xls

 

[simon]

Thanks Sheelo, I downloaded the file and compared your spreadsheet to mine. I
think my problem is being caused by the formula that creates the two lists on
sheet1...

{=IF(ROWS(A$11:A17)<=SUM(COUNTIF(RootStatus,Rating)),INDEX(Root,SMALL(IF(RootStatus=Rating,ROW(Root)),ROWS(A$11:A17))-MIN(ROW(Root))+1),"")}

If I copy the formula down from cell A11 to A30 but say only A11 to A20
contains any content, the repeat of these cells is blank for 10 cells on
Sheet 2. Then the repeat starts again. If I delete the formula from the blank
cells (i.e. A21:A30) your formula repeats the list content fine.

Any idea how I can fix this?

Thanks again

 

[Sheeloo]

See the updated file at
http://wikisend.com/download/543218/RepeatWords.xls

Basically COUNTA(Sheet1!$A$11:$A$40) was used in the formula to count the no
of key words.
I have put a formula (=COUNTA(A11:A80)-COUNTIF(A11:A80,0)) to do that in B10
of Sheet1 and used that in the formula
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,Sheet1!$B$10)=0,Sheet1!$B$10,MOD(COLUMN()-1,Sheet1!$B$10)),0)

 

[simon]

Thanks, I downloaded the file again but the formula looks the same and gives
me the same problems as before.

 

[Sheeloo]

Can you mail the file to me or upload to wikisend.com and paste the link
here... ?
Do remove any personal/confidential information.

 

[simon]

Thanks, I have uploaded the spreadsheet here
http://wikisend.com/download/466386/Keyword Spreadsheet.xls

 

[simon]

Hi Sheeloo, did you get chance to look at the file I uploaded?

Thanks