offsetting a list every 3 then skipping 2 rows with "highlight duplicate conditional formatting"

[walkster220]

Hello,need help with something simple but rather complex for me. i have a large list of 3 digit numbers in Column A. ex.(123)..I would like each digit separated in its own column (text to column) i would like these copied in a series of 3 with a space of 2 rows in between each series. Each Example A1,A2,A3 "BLANK ROW" A2,A3,A4 "BLANK ROW" A3,A4,A5 "BLANK ROW".and the process repeats with the list provided i would prefer to be able to drag the formula down with the mouse. will contain a group of 9 digits....I can use the blank rows as a divider.. Each group need to be condition formatted separately for duplicate highlighting..


.also is it possible for the patterns created by the formatted conditioning to become searchable to find exact matches?

all help is welcome thank you

[/IMG]

 

[oldbrewer]

255			2	5	5
905			9	0	5
533			5	3	3
683
002
			9	0	5
			5	3	3
			6	8	3
			5	3	3
			6	8	3
			0	0	2
	this macro achieves the above
	k = 1
	myrow = myrow + 1
	10 Cells(myrow, 4) = Left(Cells(k, 1), 1)
	Cells(myrow, 5) = Mid(Cells(k, 1), 2, 1)
	Cells(myrow, 6) = Right(Cells(k, 1), 1)
	myrow = myrow + 1
	Cells(myrow, 4) = Left(Cells(k + 1, 1), 1)
	Cells(myrow, 5) = Mid(Cells(k + 1, 1), 2, 1)
	Cells(myrow, 6) = Right(Cells(k + 1, 1), 1)
	myrow = myrow + 1
	Cells(myrow, 4) = Left(Cells(k + 2, 1), 1)
	Cells(myrow, 5) = Mid(Cells(k + 2, 1), 2, 1)
	Cells(myrow, 6) = Right(Cells(k + 2, 1), 1)
	myrow = myrow + 3
	k = k + 1
	If k > 3 Then GoTo 100
	GoTo 10
	100 End Sub

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[walkster220]

do i have to to open view code and paste this macro in? and how can i add conditional formatting to each series without doing it manually is that possible?

 

[oldbrewer]

record a simple macro eg just select cell a1 got to macro delete the line that starts Range( and paste in my macro


con formatting do just first block then use format painter

 

[WAYNE_TAI]

Don't need Macros.. take advantage of the MOD() function.. here you go.

=IF(MOD(ROWS($C$1:$C1)-1,5)+1<=3,MID(INDEX($A:$A,ROUNDUP(ROWS($C$1:$C1)/5,0)+MOD(ROWS($C$1:$C1)-1,5)),COLUMNS($C1:C1),1),"")

 

[rpaulson]

try this:

Sub do_it()

wr = 2

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

For x = 0 To 2

n = Format(Cells(r + x, "A"), "000")

Cells(wr + x, "E") = Left(n, 1)
Cells(wr + x, "F") = Mid(n, 2, 1)
Cells(wr + x, "G") = Right(n, 1)

Next x

With Range(Cells(wr + x - 3, "E"), Cells(wr + x - 1, "G"))
    .FormatConditions.Delete


    .FormatConditions.AddUniqueValues
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).DupeUnique = xlDuplicate
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
End With



wr = wr + 5

Next r

End Sub

hth,

Ross

 

[walkster220]

Woww!!thanks you all ... Ross did it for me tho