If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

How to create repeated cell values

January 12th, 2019, 02:29

Hello all..
This is my first post in the forum. I'm trying to automatically generate some data. Basically for any set of given numbers, the macro should create repeated numbers in two columns (just like shown in table below). Example I have 3 values 1,2,4 in column A, I need the macro to create 9 rows of data with each value repeating for the input data. Not sure I'm able to explain it clearly but the table might help. The values can be 100 of records that need to be repeated 100x100 for each value.
Thanks in advance for help.

Comment

The data can be read from column A. The rearranged data in columns B and C. Please see the table. This example is using consecutive numbers in Column A, but the macro should work with any range of numbers. Thanks.

Comment

For each value in column A their should be data in Column in B and C. Column C is just repeat of data in column A. In the example in my previous post there are 'N' number of records (5 in this example). For each of these records I need NxN rows of data (25) in Column B and C. Column B will show the first record in Column A N times and the data in Column C will show Column A data N times. Next Column B will show the second record N times and Column C will show Column A data N times..and it will keep repeating till it reaches NxN number of records. Hope this explanation clears things or I can attach a spreadsheet with the example. Another example is shown below.
Cell A1 is a header with data starting at A2.

Data

Result

Result

7

7

7

12

7

12

100

7

100

506

7

506

12

7

12

12

12

100

12

506

100

7

100

12

100

100

100

506

506

7

506

12

506

100

506

506

Comment

Sub RearrangeData()
Dim x, y, e, i As Long, ii As Long
With ActiveSheet
x = .Cells(1).CurrentRegion.Columns(1)
With CreateObject("scripting.dictionary")
For i = 2 To UBound(x, 1)
If x(i, 1) <> "" Then .Add x(i, 1), Nothing
Next
ReDim y(1 To .Count * .Count, 1 To 2)
For Each e In .keys
For i = 1 To .Count
ii = ii + 1: y(ii, 1) = e
Next
Next
ii = 0
For i = 1 To UBound(y, 1) Step .Count
For Each e In .keys
ii = ii + 1: y(ii, 2) = e
Next
Next
End With
With .[b2]
.Resize(1100, 2).Clear
.Resize(UBound(y, 1), 2) = y
End With
End With
End Sub

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.