Extract specific data from a spreadsheet

Lesjoan01

New Member
Joined
Mar 29, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]Company
[/TD]
[TD]Location
[/TD]
[TD]Payroll Number
[/TD]
[TD]Title
[/TD]
[TD]Firstname
[/TD]
[TD]Lastname
[/TD]
[TD]Gender
[/TD]
[TD]Job Title
[/TD]
[TD]Car or Cash
[/TD]
[TD]Cash Amount
[/TD]
[TD]Hours per week
[/TD]
[TD]Salary
[/TD]
[TD]Age
[/TD]
[TD]Start Date
[/TD]
[TD]Years Service
[/TD]
[/TR]
[TR]
[TD]ABC Ltd
[/TD]
[TD]Site 1
[/TD]
[TD]010
[/TD]
[TD]Mr
[/TD]
[TD]Paul
[/TD]
[TD]Smith
[/TD]
[TD]M
[/TD]
[TD]IT Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£52,000
[/TD]
[TD]41
[/TD]
[TD]02/08/1995
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]020
[/TD]
[TD]Miss
[/TD]
[TD]Mary
[/TD]
[TD]Jones
[/TD]
[TD]F
[/TD]
[TD]PA
[/TD]
[TD]Cash
[/TD]
[TD]£1,500
[/TD]
[TD]40
[/TD]
[TD]£34,000
[/TD]
[TD]35
[/TD]
[TD]12/11/2000
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]ABC Ltd
[/TD]
[TD]Site 1
[/TD]
[TD]030
[/TD]
[TD]Mr
[/TD]
[TD]Steve
[/TD]
[TD]Green
[/TD]
[TD]M
[/TD]
[TD]Finance Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£50,000
[/TD]
[TD]40
[/TD]
[TD]15/03/1998
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]GHI Ltd
[/TD]
[TD]Site 3
[/TD]
[TD]040
[/TD]
[TD]Mrs
[/TD]
[TD]Sarah
[/TD]
[TD]Brown
[/TD]
[TD]F
[/TD]
[TD]Head of HR
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£48,000
[/TD]
[TD]39
[/TD]
[TD]23/07/2000
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]050
[/TD]
[TD]Mrs
[/TD]
[TD]Jane
[/TD]
[TD]Greaves
[/TD]
[TD]F
[/TD]
[TD]L&D Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£46,000
[/TD]
[TD]41
[/TD]
[TD]07/10/1997
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]GHI Ltd
[/TD]
[TD]Site 3
[/TD]
[TD]060
[/TD]
[TD]Mr
[/TD]
[TD]Graham
[/TD]
[TD]Stephens
[/TD]
[TD]M
[/TD]
[TD]Foreman
[/TD]
[TD]Cash
[/TD]
[TD]£1,200
[/TD]
[TD]37
[/TD]
[TD]£29,000
[/TD]
[TD]37
[/TD]
[TD]22/05/2005
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]070
[/TD]
[TD]Mr
[/TD]
[TD]Len
[/TD]
[TD]Cloth
[/TD]
[TD]M
[/TD]
[TD]Shift Supervisor
[/TD]
[TD]Cash
[/TD]
[TD]£1,400
[/TD]
[TD]40
[/TD]
[TD]£31,000
[/TD]
[TD]40
[/TD]
[TD]06/03/1999
[/TD]
[TD]19
[/TD]
[/TR]
</tbody>[/TABLE]
Hi
I want to extract specific data from a spreadsheet, an example of the data is above

I want to initially extract data from columns:

A
B
C
E
J
L
M

and then place that data on a separate sheet. Is there a formula to do this?

Can anyone help?

Big Thanks
Lesley
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Name your data table as Table1. Then open a blank query in Power Query/Get and Transform. Insert this Mcode on the Advance Tab

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Location", type text}, {"Payroll Number", Int64.Type}, {"Title", type text}, {"Firstname", type text}, {"Lastname", type text}, {"Gender", type text}, {"Job Title", type text}, {"Car or Cash", type text}, {"Cash Amount", type text}, {"Hours per week", Int64.Type}, {"Salary", type text}, {"Age", Int64.Type}, {"Start Date", type any}, {"Years Service", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Company", "Location", "Payroll Number", "Firstname", "Cash Amount", "Salary", "Age"})
in
    #"Removed Other Columns"
 
Upvote 0
Hi Alan,

that is great, thank you very much.

One other question (if you don't mind) - If I wanted to extract all the data for a specific company would I enter {{"Company = ABC Ltd", type text}

Sorry, I am new to power query.

Many thanks

Lesley
 
Upvote 0
Ok, that's great.

Thanks very much for you assistance with my query.

Regards

Lesley
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top