automatically filling columns

[Bax]

Hi All,

I've attached a file with 2 tabs.
First tab "PO_Summary" has a PO number, Qty and a Part in column A, B and C. A part can occur multiple times with different PO numbers.
Second tab "PO_Detail" has a PO number and a Part in column A and B.

What I would like to have automated, is the filling of the PO numbers in the "PO_Detail" tab with the corresponding PO numbers from the first tab "PO_Summary".
The parts in the 2nd tab (column B) are copied / paste from an original file, also with the correct amount of lines.
So in the attached file, you see part ABC occur 5x with PO 123, part DEF occur 10x with PO 456 and part ABC again for 15x with PO 789. In the second tab, column B is then already filled with 5x ABC, 10x DEF and 15x ABC.

The macro will now need to fill the first column A of the second tab with 5x 123, 10x 456 and 15x 789.

Is there any quick and easy way to program this?

Many thanks in advance for your time and assistance, if the above is not clear, please let me know!

Kind regards,
Peter

[kbkumar]

Hi - a combination of Index and Match should work here unless there is any specific need of looking for a macro:

Use this formula in cell A3 and drag it all the way down:

Please Login or Register  to view this content.

since you have ABC appearing in cell C3 and C5 of PO_Summary tab, you will not see 789 in the PO_Detail sheet (since lookups take into account the first result). Howeve, in real data, I assume such cases wont exist.

Hope this helps.

[kbkumar]

Just in case a code is required:

Please Login or Register  to view this content.

[cinarbe]

Hi kbkumar!
I recently joined the forum and now noticed a simple point, that I was long struggling with:
I always had hard time referencing cells in loops with counters.
Macro recorder suggests the use of Range("A1") by default and I always tend to use the Cells(1,1) format.
From your message above I understand that the correct way to reference Range is as in example:

Please Login or Register  to view this content.

Is that true or are there other forms that can be of better use in such loops?
Thank you in advance!

[kbkumar]

Hi Cinarbe,

Welcome to the forum. I am afraid to say that you will have to start a new thread for your query to be compliant with the forum rules.

Thanks.

[Bax]

Hi kbkumar,

Thanks for your time looking into this.

Your formula is partially working, as indeed I would need to see the 789 in the PO_Detail sheet as well...
So the first 5 lines of part ABC need to show 123, then part DEF needs to show 456 (which is happening perfectly with your INDEX formula).
However, then the next 15 lines of ABC would need to show that specific PO number 789 from the PO_Summary sheet...
And with your formula, it is showing the 123 PO again.

So hopefully there is something that can be done to fix this...

Appreciate your time and help in this!!!

Regards,
Peter

[nilem]

Hi Peter, try this

Please Login or Register  to view this content.

[Bax]

Hi Nilem,

Perfect, thanks!!

Quick additional question, if I want to insert columns in the PO_Summary sheet, so that PO# will be column D, Qty column G and PN column H (instead of the current columns A, B and C in the testfile) what changes do I have to make in your code to have it working?
Just trying to understand what the code is doing exactly...

Thanks again!!

Regards,
Peter

[nilem]

a few changes

Please Login or Register  to view this content.

[Bax]

Perfect!!! Many thanks!!!

Star added ;-)

Regards,
Peter