Replacing Cells While Duplicating Range for N times

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Code	Name	Shelf Stock	Cluster			Cluster		Number of Times	4
2	97	Item 1	14	XL			SCCC
3	98	Item 2	15	XL			SCUP
4	99	Item 3	5	XL			SCMG
5	100	Item 4	18	XL			SCCM
6
7
8	Code	Name	Shelf Stock	Cluster
9	97	Item 1	14	SCCC
10	98	Item 2	15	SCCC
11	99	Item 3	5	SCCC
12	100	Item 4	18	SCCC
13	97	Item 1	14	SCUP
14	98	Item 2	15	SCUP
15	99	Item 3	5	SCUP
16	100	Item 4	18	SCUP
17	97	Item 1	14	SCMG
18	98	Item 2	15	SCMG
19	99	Item 3	5	SCMG
20	100	Item 4	18	SCMG
21	97	Item 1	14	SCCM
22	98	Item 2	15	SCCM
23	99	Item 3	5	SCCM
24	100	Item 4	18	SCCM
25
26

A9

Formula:

=IFERROR(INDEX(A$2:A$5,IF(COUNTA($A$2:$A$5)*$J$1 < ROWS(A$9:A9),"",MOD(ROWS(A$9:A9)-1,$J$1)+1)),"")

Copy across C column.

D9

Formula:

=IFERROR(INDEX($G$2:$G$5,IF(COUNTA($A$2:$A$5)*$J$1 < ROWS(A$9:A9),"",INT((ROWS(D$9:D9)-1)/$J$1)+1)),"")

Check the attached file.

Hi Shukla,

I looked into the document you sent me and it works. I'm now working on making a document where i can paste the range in sheet 1, alter all the variables (Site list, number of times) in Sheet 2 and have the result show up in sheet 3. But i'm having a hard time understanding the function.

If it's not too much to ask, could you please explain to me the different sections of the function and what they do?
Eg: What does IFERROR do in this instance or Why use COUNTA? And i can't understand why you used the MOD function as well.

Thanks so much!

Hi Shukla,

So i've been trying to modify your formula to work on an unlimited range across two sheets. The reason why i want to do is because i need to be able to just paste the range to a placeholder, insert the outlet codes and set the number of items and get the result in a different sheet. This workbook will be used by a bunch of people so i need it to work flawlessly.

Here's what i did.
Worksheets:

1. Sheet 1: Data - Where i paste the range, site codes and number of items
2. Sheet 2: Outlet-wise Shelf Stock - Where the end result is displayed

Here's the modified formula:

[Getting a firewall error if i posted this with the formula... How do i post it? ]

I'm being returned the space set to return if any errors. But i don't understand what's wrong.

I would much appreciate if you could have a look at this.
Nick

If your formula contains an < Succuri will block it. Try adding a space after the < Better still, attach a file. For what it's worth, I would have used a totally different (simpler) approach to this problem:

A9, copied across and down:
=IF($D9="","",INDEX(A$2:A$5,1+MOD((ROWS(A$9:A9)-1),$J$1)))

D9, copied down:
=IFERROR(INDEX($G$2:$G$5,MATCH(0,INDEX(--(COUNTIF($D$8:D8,$G$2:$G$5)=$J$1),0),0)),"")

Hi Glenn, thank you for the new formula. Could you please explain to me how it works. Thanks!

=IF($D9="","",INDEX(A$2:A$5,1+MOD((ROWS(A$9:A9)-1),$J$1)))

Red: If d9 is blank, return blank
Gold: This is simply a counter. As you drag it down, it returns 1,2,3,4,1,2,3,4,1,2,.....

Basically in A9, rows(A9:a9) returns 1.
1-1=0
the remainder when you divide 0 by 4 is 0.
Add 1 to that and you get 1.

In A10, rows(A$9:a10) returns 2.
2-1=1
Divide by 4 and the remainder is... 1

add 1 and you get... 2

Green: return the corresponding value from here.


=IFERROR(INDEX($G$2:$G$5,MATCH(0,INDEX(--(COUNTIF($D$8:D9,$G$2:$G$5)=$J$1),0),0)),"")

Counts the No of times each of the values in G2 to G5 has appearred in the range D$8:D9. It returns TRUE until the count of the result = 4. Then it returns false, so the INDEX($G$2:$G$5 moves on to the next value and returns it up to 4 times...

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Originally Posted by Razzlesama

Thanks.

The formula works but with some errors. I attached the file here. I'm changing this so the people who'll be using this file could just copy the range and get the result without changing the formula.

I don't understand why you are not follow the steps provided in previous post. We always give you efficient and closer solution as per description provide from your end. Even in your last post you didn't described the errors you are getting. Also I would suggest you to please reduce the range as per need your file always get hang.....

Originally Posted by shukla.ankur281190

I don't understand why you are not follow the steps provided in previous post. We always give you efficient and closer solution as per description provide from your end. Even in your last post you didn't described the errors you are getting. Also I would suggest you to please reduce the range as per need your file always get hang.....

I would have described the errors IF I KNEW WHAT THE ERRORS WERE! I'm a novice. I don't know what's happening or why it's not working. Hence why i'm here talking to you lovely people trying to figure out a solution. So i figured i should just attach the file without wasting time trying to explain something i don't even understand!

And the reason why i'm trying to change things up is to try and understand the formula. I don't just want a solution, I WANT TO LEARN!

Here's what i did:

1. Made two new sheets. : Sheet number 1 contains the data to be duplicated while sheet 2 is for the result to be displayed.
2. Copied Glenn's formula to the corresponding cell in the second sheet.
3. Changed the ranges in the formulas to match the new locations. (IE: A2:A5 was changed to A2: A102010 - Reason is so i can just paste the values without ALTERING THE FORMULA EVERY TIME)

Here's the deal. Duplicating data like this is a recurring exercise in my workplace. And more than twenty people will be using the file i build for that purpose. Some of them are even less knowledgeable about excel formulas than me. So I think it's better if they don't have to tamper with the formula every time they wanna use it. Because the number of items in this range to be copied will change every time they have to do it. And each cluster has different amounts of sites.

There's 10 columns in the actual worksheet i'm working on. And some ranges may have more than 2500 rows. And if i was to use this file for a cluster like "M" which has 42 outlets, the result will have 2500*42=105,000 rows. The example i gave you only had 4 for ease of understanding. So it goes without saying i have to modify the formula to suit my needs!

I am now totally confused. Your sheet is not clear!! Is it the case that sometimes, but not always that you want the cluster code copied down 4 times? If it is not ALWAYS four, then we need to add a column next to each cluster code to tell excel how many times cto copy that code down. You need to define some rules here....

I no longer know what you want done. So, using realistic data, please explain again.

I think that I follow you.

Sheet1, A2, copied down:
=IF($K2="","",INDEX(Data!A$2:A$36,1+MOD((ROWS(A$2:A2)-1),Data!$O$1)))
This will copy the entire set of stock lines (35) down 4 times (once for each outlet code)

Sheet1, K2, copied down:
=IFERROR(INDEX(Data!L$2:L$5,MATCH(0,INDEX(--(COUNTIF($K$1:K1,Data!L$2:L$5)=Data!$O$1),0),0)),"")
This will copy each outlet code 35 times before moving on to the next one.

Data, O1,
35
(there being 35 lines of product each of which has to be replicated 4 times, once for each outlet code).

If this not correct, please explain - using the sort of terminology that I have used - what you need. Keep it short and absolutely clear.

I think I'll become an alcoholic...

What on earth do you think this bit is doing???

INDIRECT(CONCATENATE("D",Data!$O$2)

At the risk of getting even stranger modifications... but trying to remove the possibility of further human error, I have created 3 named ranges (CTRL-F3 to view them). They will adjust automatically (up to 10000 rows) to the size of your dataset.

Formulae now refer to the named ranges.

Glenn sits back... waits for a response and reaches for the corkscrew....

I wondered if you would spot the file name...

The formulae in the sheet have not changed. They work as previously. However, instead of manually adjusting ranges I used 3 named ranges to automate.One was this (Outlet_Rng):
=Data!$L$2:INDEX(Data!$L$2:$L$10000,COUNTA(Data!$L$2:$L$10000))


which establishes the range of the cells containing your outlet codes.
Red: Begin in this cell
Green: look down this range as far as
Blue: the last non-blank cell

Items_Rng was similar. he last one was Items_Cnt, a count of the number of stock items:

=COUNTA(Data!$A$2:$A$10000)

That hardly needs an explanation. That's it. Use the names of the named ranges, instead of cell references in your formulae and... job done.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.