Sleeper: Mark the content with the same values in once cell in red

[berlusconi]

Hello everyone,


An Excel table with many columns and rows is available. The cells have different or the same values. The output columns contain different numbers of rows.


Example Excel


Column 1
Column 2
Column 3
Column 4
Column 5


1234567


12345





1234





12345

12345
12345


12345


12345





12345






The program uses this table to create a PDF file with values in corresponding cells. The number of columns in the PDF is fixed. The number of lines in the PDF is variable.

PDF


A
B
C
D
E
F
G
H


Text 1






1234567
12345


Text 2






1234


Text 3






12345
12345
12345


Text 4






12345
12345


Text 5






12345



Sometimes it happens that 2 or 3 identical numbers are listed in a cell in the created PDF.


Wish 1:
I would like the program to recognize which cell in column H contains 2 or 3 identical numbers and to mark the 2nd or possibly 2nd and 3rd numbers in red.


Wish 2:
I would like the program to recognize which cell in column H has 2 or 3 identical numbers and not write the 2nd or possibly 2nd and 3rd into the cell.


I would like to implement the simplest of the solutions mentioned.

I hope some experts can help :) I'm a novice when it comes to vba. :(


Thank you very much for your support and best regards!

Marko

[June7]

You have code that creates PDF? Post it or provide workbook.

[berlusconi]

Hello,

thank you very much for the swift answer.

I'm afraid I can not share the code due to company policy :/

If there is a way to solve this or even give me a hint, would be much appreciated.

[June7]

Could possibly link to Excel in Access and build a pretty report for PDF export. Could probably do it without any VBA.

For Wish 2, I do have code but need column A to have a unique identifier for each row. Set a VBA reference to Microsoft ActiveX Data Objects x.x Library.
Consider:

Sub UnPivot()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, strC As String, r As Integer, x As Integer
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"

rs.Open "SELECT 'Text ' & [ID] AS RID, [Column1] AS Data FROM [Sheet1$] WHERE IsNumeric([Column1]) " & _
"UNION SELECT 'Text ' & [ID], [Column2] FROM [Sheet1$] WHERE IsNumeric([Column2]) " & _
"UNION SELECT 'Text ' & [ID], [Column3] FROM [Sheet1$] WHERE IsNumeric([Column3]) " & _
"UNION SELECT 'Text ' & [ID], [Column4] FROM [Sheet1$] WHERE IsNumeric([Column4]) " & _
"UNION SELECT 'Text ' & [ID], [Column5] FROM [Sheet1$] WHERE IsNumeric([Column5])", cn, adOpenStatic, adLockOptimistic, adCmdText
strC = rs!RID
r = 10
x = 1
Do While Not rs.EOF
If strC <> rs!RID Then
r = r + 1
strC = rs!RID
x = 1
End If
Sheet1.Range("A" & r).Value = IIf(x = 1, rs!RID, Null)
Sheet1.Range("H" & r).Value = rs!Data
r = r + 1
x = x + 1
rs.MoveNext
Loop
End Sub

[Aussiebear]

Welcome to VBAX berluconi. Seriously your request is so vague that I'm surprised that June7 has been able to respond. You said it would be difficult to provide a sample given company policy. My indication to you is simply this, if your company is afraid of public scrutiny, don't post on a public forum. If you want help then please post a sample workbook with what you face and what you would like.

[Paul_Hossler]

Wish 1:
I would like the program to recognize which cell in column H contains 2 or 3 identical numbers and to mark the 2nd or possibly 2nd and 3rd numbers in red.


Wish 2:
I would like the program to recognize which cell in column H has 2 or 3 identical numbers and not write the 2nd or possibly 2nd and 3rd into the cell.



These seem contradictory to me - make it red or leave it off???

Is the attached Sample.xlsx close?

[June7]

@Paul_Hossler, OP said they would be happy with solution to either wish so not contradictory. I found the second easier.

[Paul_Hossler]

@Paul_Hossler, OP said they would be happy with solution to either wish so not contradictory. I found the second easier.

Well, either way is simple

I would not think that using Access, adding a unique ID, and setting a ActiveX reference is simpler


Could possibly link to Excel in Access and build a pretty report for PDF export. Could probably do it without any VBA.

For Wish 2, I do have code but need column A to have a unique identifier for each row. Set a VBA reference to Microsoft ActiveX Data Objects x.x Library.

[Paul_Hossler]

Something to try - this deletes repeating rows




Option Explicit


Sub FormatData()
Dim inWS As Worksheet, pdfWS As Worksheet
Dim pdfFilename As String
Dim inData As Range
Dim r As Long, c As Long, o As Long


Application.ScreenUpdating = False

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("PDF").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Worksheets.Add.Name = "PDF"
Set pdfWS = Worksheets("PDF")
Set inWS = Worksheets("Input")

Set inData = inWS.Cells(1, 1).CurrentRegion

o = 1

With inData
For c = 1 To .Columns.Count
For r = 2 To .Rows.Count
If Len(.Cells(r, c).Value) > 0 Then
pdfWS.Cells(o, 1).Value = .Cells(1, c)
pdfWS.Cells(o, 8).Value = .Cells(r, c).Value
o = o + 1
End If
Next r
Next c
End With




With pdfWS
.Select
.Cells(1, 1).Resize(o - 1, 8).RemoveDuplicates Columns:=Array(1, 8), Header:=xlNo

For r = .Cells(1, 1).CurrentRegion.Rows.Count To 2 Step -1
If .Cells(r, 1).Value = .Cells(r - 1, 1).Value Then .Cells(r, 1).ClearContents
Next

pdfFilename = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1) & ".PDF"

Application.DisplayAlerts = False
On Error Resume Next
Kill pdfFilename
On Error GoTo 0
Application.DisplayAlerts = True

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFilename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With


Application.ScreenUpdating = True


MsgBox "Done - " & pdfFilename & " created"


End Sub

[June7]

Nice code but the output is not quite what OP showed in sample. Output is taking each row and pivoting to vertical orientation. This can be seen with Text 3 and Text 4 outputs. I made the same mistake in my first code attempt.

And by 'easier' I meant I found Wish 2 easier to code than Wish 1.

[Paul_Hossler]

Output is taking each row and pivoting to vertical orientation. This can be seen with Text 3 and Text 4 outputs. I made the same mistake in my first code attempt.

Not sure what you mean. Wish 2 was to delete duplicate lines

Note that OP's input has 1234 and 12345 in Column 3, but the 1234 was dropped in the OP's PDF sample


31144

[June7]

It wasn't dropped, it is in Text 2 because it is read from row 2

Again, the output is from each row rotated to vertical orientation.

I know Wish 2 is to remove duplicates - duplicates from Column H (as stated by OP) and those duplicates result from rotating each row. When duplicates are removed from Column H, the output is:



Text 1






12345









1234567












Text 2






1234












Text 3






12345












Text 4






12345












Text 5






12345

[Paul_Hossler]

So you're saying that Column 1-5 has nothing to do with the result, it's the row number that maps to Text N

31145

You might be correct, but I didn't get that out of it

[June7]

I didn't either in my first coding attempt. I did what you did. Then I took another look at the output table and the only way to get Column H is to rotate each row, not just stack the columns.

[Paul_Hossler]

Asumming that you're correct, Wish 2 could have been easier to follow


31146

[p45cal]

Bearing in mind that the numbers seem to be wanted in the same cell, from this:

which cell in column H has 2 or 3 identical numbers
the attached opts for deleting the identical numbers (rather than highlighting them).
It's Power Query, so if the source data changes, it needs refreshing (by right-clicking the results table and choosing Refresh).

31148

[Paul_Hossler]

Bearing in mind that the numbers seem to be wanted in the same cell, from this:




Sometimes it happens that 2 or 3 identical numbers are listed in a cell in the created PDF.


I thought about that but since the end result seems to be a PDF file, I decided that they would get lost anyway

[June7]

Power Query is rotating data but not getting row spacing displayed in example.

[berlusconi]

Guys, thank you very much for the effort and great suggestions!

I know there was not enough informations for you to provide a suitable answer. But the suggestions helped!

I have managed to compile the code that works.

Thanks again!

[Aussiebear]

Any chance of showing us the code, so that others can learn?