Home

Need Help Sorting Data

%3CLINGO-SUB%20id%3D%22lingo-sub-807624%22%20slang%3D%22en-US%22%3ENeed%20Help%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807624%22%20slang%3D%22en-US%22%3E%3CP%3EI%20receive%20data%20about%20several%20locations%20within%20the%20company%2C%20and%20need%20to%20send%20that%20data%20on%20(after%20much%20manipulating)%20to%20each%20place%20individually.%20The%20easiest%20way%20to%20do%20this%20would%20be%20to%20pick%20the%20data%20from%20the%20large%20list%2C%20and%20put%20it%20into%20a%20new%20tab%20so%20that%20the%20trend%20graphs%20etc%20can%20be%20created%20from%20a%20single%20tab.%20and%20manually%20doing%20it%20would%20take%20too%20long%20with%20the%20amount%20of%20data%20there%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20a%20dummy%20dataset%20to%20figure%20out%20how%20to%20do%20this%20before%20using%20the%20confidential%20data%20that%20i%20work%20with.%20My%20problem%20is%20I'm%20not%20sure%20where%20to%20start.%20I%20assume%20there%20would%20be%20some%20kind%20of%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20Name%20%3D%20%22X%22%2C%20%5Bcommand%20to%20make%20it%20paste%20into%20the%20column%20of%20the%20same%20name%20on%20another%20tab%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStatement%2C%20but%20I%20have%20no%20background%20in%20coding%20languages%20so%20I%20am%20lost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20image%20the%20locations%20are%20represented%20by%20the%20%22name%22%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20895px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127206i8FF97F573BC6CE5B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22dummy%20data.png%22%20title%3D%22dummy%20data.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-807624%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815634%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393231%22%20target%3D%22_blank%22%3E%40astralomega%3C%2FA%3E%26nbsp%3BIf%20I%20am%20understanding%20your%20need%20correctly%2C%20you%20are%20basically%20wanting%20to%20sort%20this%20data%20by%20location%20(column%20K)%20and%20then%20have%20an%20individual%20report%20for%20each%20location%20to%20send%20off.%20Correct%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20I%20would%20personally%20recommend%20creating%20a%20using%20Excel's%20PivotTable%20feature%26nbsp%3Band%20break%20out%20your%20data%20by%20location.%20Depending%20on%20how%20often%20you%20update%20this%20data%20table%2C%20you%20can%20refresh%20your%20PivotTable%20and%20everything%20will%20update.%20Additionally%20-%20you%20will%20be%20able%20to%20double-click%20on%20the%20individual%20location%20in%20your%20table%20which%20will%20open%20a%20separate%20tab%20with%20your%20data%20for%20that%20location%20-%20rather%20than%20manually%20transfer%20the%20data%20to%20a%20new%20tab%20for%20each%20location.%26nbsp%3B%20Additionally%2C%20you%20can%20create%20PivotCharts%20for%20this%20data%20to%20help%20with%20your%20tending%20reporting.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20unfamiliar%20with%20PivotTable%2C%20here%20is%20a%20link%20with%20a%20quick%20tutorial.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815910%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393231%22%20target%3D%22_blank%22%3E%40astralomega%3C%2FA%3E%26nbsp%3B%2B1%20on%20the%20PivotTable%20suggestion!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20if%20you%20add%20the%20Name%20field%20to%20the%20%3CSTRONG%3Ereport%20Filter%3C%2FSTRONG%3Esection%2C%20you%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Fpivot-tables%2Fshow-report-filter-pages%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EShow%20Report%20Filter%20Pages%3C%2FA%3Eoption%2C%20which%20will%20create%20individual%20PivotTable%20worksheets%20for%20each%20name%20in%20the%20list.%20They'll%20still%20contain%20the%20underlying%20data%2C%20so%20you'll%20want%20to%20Copy%20%26gt%3B%20Paste%20Special%20%26gt%3B%20Values%20before%20distributing%2C%20but%20there's%20a%20ton%20of%20VBA%20code%20around%20to%20help%20you%20automate%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822241%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396133%22%20target%3D%22_blank%22%3E%40excelgeek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20putting%20it%20into%20a%20Pivot%20table%2C%20but%20it%20is%20not%20exactly%20what%20I%20need.%20I%20need%20to%20be%20able%20to%20turn%20the%20raw%20data%20into%20a%20slew%20of%20graphs%20that%20show%20select%20columns%20over%20time%20(since%20that%20is%20what%20the%20client%20has%20requested)%2C%20but%20the%20pivot%20table%20summarizes%20the%20data.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20double%20click%20to%20drill%20down%2C%20the%20data%20opens%20in%20a%20new%20sheet%20but%20I%20would%20prefer%20it%20if%20it%20could%20open%20into%20an%20existing%20sheet%20so%20that%20I%20could%20have%20the%20graphs%20connected%20to%20that%20data.%3C%2FP%3E%3CP%3EAlternatively%2C%20find%20a%20way%20to%20paste%20the%20data%20into%20a%20new%20sheet.%20I%20found%20more%20problems%20with%20what%20I%20am%20trying%20to%20do%20here%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F57583518%2Fhow-to-edit-vba-code-to-paste-in-a-different-location%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F57583518%2Fhow-to-edit-vba-code-to-paste-in-a-different-location%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anybody%20has%20an%20idea%20how%20to%20either%20make%20the%20drilldown%20data%20fit%20into%20cells%20chosen%20for%20chart%20data%2C%20or%20to%20make%20the%20data%20paste%20into%20a%20table%20on%20another%20worksheet%20but%20at%20the%20bottom%20of%20the%20existing%20data%2C%20I%20would%20greatly%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
astralomega
Visitor

I receive data about several locations within the company, and need to send that data on (after much manipulating) to each place individually. The easiest way to do this would be to pick the data from the large list, and put it into a new tab so that the trend graphs etc can be created from a single tab. and manually doing it would take too long with the amount of data there is.

 

I have made a dummy dataset to figure out how to do this before using the confidential data that i work with. My problem is I'm not sure where to start. I assume there would be some kind of

 

IF Name = "X", [command to make it paste into the column of the same name on another tab]

 

Statement, but I have no background in coding languages so I am lost.

 

In this image the locations are represented by the "name" column.

 

dummy data.png

 

2 Replies

@astralomega If I am understanding your need correctly, you are basically wanting to sort this data by location (column K) and then have an individual report for each location to send off. Correct? 

 

If so, I would personally recommend creating a using Excel's PivotTable feature and break out your data by location. Depending on how often you update this data table, you can refresh your PivotTable and everything will update. Additionally - you will be able to double-click on the individual location in your table which will open a separate tab with your data for that location - rather than manually transfer the data to a new tab for each location.  Additionally, you can create PivotCharts for this data to help with your tending reporting. 

 

If you are unfamiliar with PivotTable, here is a link with a quick tutorial. 

 

https://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9...

 

Hope this helps. 

@astralomega +1 on the PivotTable suggestion!

 

In addition, if you add the Name field to the report Filter section, you can use the Show Report Filter Pages option, which will create individual PivotTable worksheets for each name in the list. They'll still contain the underlying data, so you'll want to Copy > Paste Special > Values before distributing, but there's a ton of VBA code around to help you automate that.

 

HTH

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies