Home

Organizing Large Database into Few Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-83908%22%20slang%3D%22en-US%22%3EOrganizing%20Large%20Database%20into%20Few%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-83908%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20wanting%20to%20make%20a%20messy%20process%20easy.%20We%20have%20several%20different%20database%20systems%20available%20for%20employees%20to%20use.%20They%20need%20to%20sort%20through%20their%20client%20database%20and%20send%20us%20only%20a%20few%20columns%20(i.e.%20Client%20Score%2C%20First%20Name%2C%20Last%20Name%2C%20Phone%20Number%2C%20etc.).%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20find%20a%20column%20with%20a%20header%2C%20%22Client%20Score%2C%22%20from%20a%20huge%20spreadsheet%2C%20and%20return%20the%26nbsp%3Bentire%20%22Client%20Score%22%20column%20on%20a%20new%20sheet.%20This%20would%20have%20to%20be%20done%20for%20each%20column%20and%20would%20need%20to%20work%20for%20different%20databases.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShouldn't%20I%20be%20able%20to%20enter%20a%20function%20to%20look%20for%20the%20column%20header%20in%20a%20specified%20sheet%2C%20then%20return%20that%20entire%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-83908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-83923%22%20slang%3D%22en-US%22%3ERe%3A%20Organizing%20Large%20Database%20into%20Few%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-83923%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brandon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20brief%20Get%20%26amp%3B%20Transform%20in%20Excel%202016%20(or%20Power%20Query%20in%20previous%20versions)%20looks%20like%20the%20right%20tool.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20connect%20to%20you%20raw%20table%20(in%20source%20Excel%20file%2C%20or%20in%20SQL%20databases%2C..%20many%20other%20connectors)%2C%20select%20the%20column(s)%20you%20need%3B%20remove%20all%20others%20and%20load%20result%20into%20your%20Excel%20sheet.%20Simplest%26nbsp%3Bvariant%20of%20such%20query%20could%20be%20generated%20in%20few%20clicks%2C%20with%20each%20new%20source%20table%20only%20Refresh%20all%20is%20required%20(manually%20or%20programmatically).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20more%20concrete%20recommendations%20desirably%20to%20have%20more%20concrete%20information%20about%20data%20flow%20organization.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Brandon Sturgis
Occasional Visitor

I'm wanting to make a messy process easy. We have several different database systems available for employees to use. They need to sort through their client database and send us only a few columns (i.e. Client Score, First Name, Last Name, Phone Number, etc.). I'm wondering if there is a way to find a column with a header, "Client Score," from a huge spreadsheet, and return the entire "Client Score" column on a new sheet. This would have to be done for each column and would need to work for different databases. 

 

Shouldn't I be able to enter a function to look for the column header in a specified sheet, then return that entire column?

1 Reply

Hi Brandon,

 

In brief Get & Transform in Excel 2016 (or Power Query in previous versions) looks like the right tool.

 

You connect to you raw table (in source Excel file, or in SQL databases,.. many other connectors), select the column(s) you need; remove all others and load result into your Excel sheet. Simplest variant of such query could be generated in few clicks, with each new source table only Refresh all is required (manually or programmatically).

 

For more concrete recommendations desirably to have more concrete information about data flow organization.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies