Hi. I have two separate lists, one with company sector(1) and one without (2). I have merged the two lists and want to populate the (2) list with the correct sector(from the corresponding (1) list). The sheet has 140000 entries so copy and paste is simply killing me.

Can anyone help me with a formula to solve this please??
COLUMN A Column B

2Calor Gas Ltd
2Calor Gas Lt[/B]d
1BUSINESS & INDUSTRIAL 1Calor Gas Ltd



2Cambridge Manufacturing Company Ltd
2Cambridge Manufacturing Company Ltd
2Cambridge Manufacturing Company Ltd
2Cambridge Manufacturing Company Ltd
2Cambridge Manufacturing Company Ltd
2Cambridge Manufacturing Company Ltd[/B]
1PHARMACEUTICAL 1Cambridge Manufacturing Company Ltd

To be clear - as an example - Calor Gas

(sector)Row 1 column A (Empty) (Company) Row 1 column B "Calor gas" (from list 2)
(sector)Row 2 column A (Empty) (Company) Row 2 column B "Calor gas" (from list 2)
(sector)Row 3 Column A ("Business & Industrial") (Company) Row 3 column B "Calor gas" (from list 1)

I would like to create a formula that looks up Company(Column B) List 1 and corresponding sector(in column A)- then identifies matching company from list 2 (in the same column) and populates the empty cell in column A: with the correct sector from list 1 COLUMN A.

Hi
please post a sample sheet showing what you have and expected results. Easier to work with.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

please see the attached for an example and the example outcome. thankEXCEL LIST ISSUE.xlsx you

IMHO, a macro might be easier to work with as with a formula you would have to use a column other than A and then copy and paste.

This macro will look for the last row of column B and then apply the results from column A from the bottom up.

If you are not familiar with macros, this might help...

http://www.rondebruin.nl/win/code.htm

@jeffrybrown Thank you so much for adding the macro. I have taken a look at the link to help me add it and I am really struggling.

I am on excel 2016 and the ctrlF11 opens a macro tab. I cant get the VBA to open - Have tried ctrl r. SO , in short i dont know how to use the macro.

I have added it to the macro sheet that opened and tried adding it to the actual worksheet ( which it will not paste to )

On the macro sheet, it appears over 10 rows - but from here I am really lost.

Can you guide me please?

Did you have a look at the link jeffrey provided?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

From the link provided, you may have missed this...

Paste code in a Normal module

• Where to paste code
• Highlight macro to copy >> Ctrl + C >> Open your workbook
• Alt + F11 >> opens the Visual Basic Editor (VBE)
• Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
• Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

Make sure you are on the sheet you want to run the macro on before you run the macro.

In the macro provided I did not qualify the sheet.

@jeffreybrown - You are a superstar - saved me years of my life - thank you for all your help.