find the coordinates of the cell with a given value

[Yves Larbodiere]

good morning,

I'm looking for the equivalent of the excel find function to find the coordinates of the cell with a given value. The objective is to fill an Excel template from a dataframe which contains in column 1 a list of codes and in column 2 an amount to assign to the adjacent cell with the current code.

I hope I've made myself clear.

Thanks for your feedback.

Best regards

[Charlie Clark]

On 8 May 2024, at 17:45, Yves Larbodiere wrote:

> good morning,
>
> I'm looking for the equivalent of the excel find function to find the
> coordinates of the cell with a given value. The objective is to fill an
> Excel template from a dataframe which contains in column 1 a list of codes
> and in column 2 an amount to assign to the adjacent cell with the current
> code.

What have you tried so far?

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Yves Larbodiere]

Valeur =Detail_col[(Detail_col['Col4']!=0) | (Detail_col['Col5']!=0) |(Detail_col['Col6']!=0) ]

    Nb_lignes_detail, Nb_colonnes_detail = Valeur.shape[0], Valeur.shape[1]

    wb = openpyxl.load_workbook(Modele)
    ws = wb[Code_page]
    for a in range(0, Nb_lignes_detail): 
        jojo = list(Valeur.iloc[a])
        for b in range(0, Nb_colonnes_detail):
            if len(str(Valeur.iloc[a, b ]))==2 : key = str(Valeur.iloc[a, b ])[0:2]
            for row in ws.iter_rows(min_row=4, min_col=4, max_row=40,max_col=9):
          
                for cell in row:
  
                    if str(cell).find('MergedCell') != -1:
                        break
                    elif cell.value == key and str(cell).find('MergedCell') == -1:
              
                        cell.offset(column=1).value = Valeur.iloc[a, b+1 ]
                        break
                    elif str(cell).find('MergedCell') == -1:
                        # print(str(cell).find('MergedCell'))
                        cell.value = Valeur.iloc[a, b]

Valeur =         Col1    Col2 Col3  Col4    Col5    Col6
ligne12   AT   14373   AU  5383    8990   10455
ligne17   BB       0   BC     0       0   83000
ligne22   BJ   14373   BK  5383    8990   93755
ligne31   BX   22620   BY     0   22620   30485
ligne33   BZ   28740   CA     0   28740    1584
ligne35   CD    4683   CE     0    4683   44683
ligne36   CF   74869   CG     0   74869   79484
ligne39   CJ  130911   CK     0  130911  116236
ligne43   CO  145284   1A  5383  139902  209991
ligne16   CU       0   CV     0       0     300

Excel Template

[Charlie Clark]

On 8 May 2024, at 17:57, Yves Larbodiere wrote:

> Valeur =Detail_col[(Detail_col['Col4']!=0) | (Detail_col['Col5']!=0) |(Detail_col['Col6']!=0) ]

Yikes!

That's horrible code that is going to run very slowly and be impossible to debug.

I'd suggest that you write something that creates the instructions based on the dataframe, probably in the form of a dictionary that you can then use on a worksheet. It's usually the best way to handle this kind of problem.