Ask Your Question

Find and bold duplicates with python macro

asked 2019-11-15 17:38:40 +0100

secnec gravatar image

updated 2019-11-15 20:08:15 +0100

I have a python macro, that does many things with my workbook, but I would like to add to it a function that goes through the columns on the first sheet, finds duplicates by row, removes any that are found, and bolds the remaining instance. The data is already sorted, so it is enough to compare the cell to the one before.

What I was thinking, in pseudocode, is:

oSheet1 = oDoc.getSheets().getByIndex(0)
for column in oSheet1:
    for cell (starting from 2) in column:
        if cell == cell-1:

Can anyone give examples on how to do this, or if there is a better way?

Edit: Here is an example. The sheet "Raw Data" has an example of how the data is formatted. "Desired Result" is an example of what I would like to have


edit retag flag offensive close merge delete


I don't understand "finds duplicates by row", what exactly do? better, attach file with an example.

mauricio gravatar imagemauricio ( 2019-11-15 19:10:39 +0100 )edit

I mean find duplicate cells by going through the column row by row

secnec gravatar imagesecnec ( 2019-11-15 19:13:23 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-11-15 21:32:59 +0100

mauricio gravatar image


from collections import Counter

def mark_duplicate():
    BOLD = 150

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets['Raw data']
    target = doc.Sheets['Desired Result']
    cursor = target.createCursorByRange(target[0,0])

    cursor = sheet.createCursorByRange(sheet[0,0])
    data = cursor.DataArray

    for col in range(len(data[0])):
        values = tuple(zip(*data))[col]
        counter = Counter(filter(None, values))
        row = 0
        for k, v in counter.items():
            cell = target[row, col]
            cell.String = k
            if not v == 1:
                cell.CharWeight = BOLD
            row += 1
edit flag offensive delete link more


This works. Thank you kindly.

secnec gravatar imagesecnec ( 2019-11-20 18:04:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-15 17:38:40 +0100

Seen: 18 times

Last updated: Nov 15