Need help with python coding for excel

1

Hello,
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

2

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

3

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?

4

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)

5

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

6