Need help with python coding for excel

I am new to python and I work on excel everyday which is more of manual. I need to automate some part using python as I am interested in learning it. I need python gurus to help me in coding.
I have data in excel 1 which I need to read and then go to excel 2 find them in excel 2 and get the values in columns.
Below is the example data I have and rules on how to populate data

  1. Read row 1 & column 1 from excel 1, go to excel 2 find AAAAAA and get value from column A and write to column 2 row 1 in excel 1
  2. Always skip middle row if column 1 in excel 1 has more than two entries
  3. Read row 3 & column 1 from excel 1, go to excel 2 find AAAAAA and get value from column B and write to column 2 row 3 in excel 1
           Excel 1                                                                                     Excel 2
row        column 1        column 2                                            column X        column A       column B
1            AAAAAA                                                                   CCCCCC          20                    30
2           AAAAAA                                                                     AAAAAA           40                     50
3           AAAAAA                                                                     BBBBBB            10                      30
4           BBBBBB
5           BBBBBB
6           CCCCCC
7            CCCCCC
8            CCCCCC

Result should be like below
Excel 1
row       column 1           column2
1            AAAAAA              40
2            AAAAAA
3            AAAAAA               50
4            BBBBBB               10
5            BBBBBB               30
6            CCCCCC             20
7            CCCCCC
8            CCCCCC              30

Please anyone help me on this

If you are using Excel, why not just use VBA?

I am not understanding the logic here. Why if there are 3 AAAAA, does column 2 get 40 for the first one, the second one get nothing and the 3rd one get 50? What if there were 5 AAAAA, would is the logic in pairing up the values to the specific row of the same letter set?

That’s how I see data in my excel and there will be no entries more than three. Pairing should work exactly work as in my logic. I started learning python and not at the point to code this logic yet. So don’t want to do in VBA (again start learning VBA)

OK, so what Python code have you tried so far?