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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
33 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
9 Replies