Excel list help please :(

markrose · #1 05-03-2016, 01:39 AM

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

markrose · #2 05-03-2016, 02:52 AM

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.

Pecoflyer · #3 05-03-2016, 03:48 AM

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

markrose · #4 05-03-2016, 07:23 AM

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

jeffreybrown · #5 05-03-2016, 03:31 PM

Code:

Can anyone help me with a formula to solve this please??

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.

Code:

Sub FillRangeFromAbove()
    Dim LR As Long
    Dim i As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If Range("A" & i - 1).Value = "" Then
            Range("A" & i - 1).Value = Range("A" & i).Value
        End If
    Next i
End Sub

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

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

markrose · #6 05-04-2016, 02:02 AM

@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?

Pecoflyer · #7 05-04-2016, 04:15 AM

Did you have a look at the link jeffrey provided?

jeffreybrown · #8 05-04-2016, 04:33 AM

Quote:

ctrlF11 opens a macro tab

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

Quote:

Open the VBA editor

Open the VBA editor with the shortcut Alt+F11.

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.

markrose · #9 05-04-2016, 06:14 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import list from Excel into drop-down list into word	ahw	Word VBA	43	02-28-2020 08:11 PM
Excel Combobox List	VBA-User	Excel Programming	4	01-07-2016 02:46 AM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data	Faldinio	Word VBA	7	10-19-2014 06:03 AM
How to export contact list to Excel	ksimmonds	Outlook	2	12-07-2011 09:33 PM
Searchable list in excel?	leahca	Excel	7	11-24-2011 04:44 AM