Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-17-2022, 06:21 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default Convert an excel file with many fields into one with a few specific fields

I could use some help. Long story short, I had an old website that could take information and give me the correct format I needed. My new website cannot. I have a basic knowledge of excel and spreadsheets.



When I get to download a file, I get a file that is called orders with many extra columns and data I need to be gone.

I need to convert that file into a tournament, with the exact columns, names, etc., with just that information.

The Orders file has all the information, but it is not in the correct order, and I can't have the extra data, or the program I use will not be able to read it.

I would greatly appreciate some help.

Thanks again, guys!
Attached Files
File Type: xls Tournament File.XLS (1,006 Bytes, 8 views)
File Type: xlsx orders.xlsx (10.7 KB, 8 views)
Reply With Quote
  #2  
Old 11-18-2022, 12:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Cross posted at Convert an excel file with many fields into one with a few specific fields
Please always add links to cross posts. Please read A message to forum cross posters - Excelguru to understand why this is important
Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 11-18-2022, 01:41 AM
ArviLaanemets ArviLaanemets is offline Convert an excel file with many fields into one with a few specific fields Windows 8 Convert an excel file with many fields into one with a few specific fields Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

An example of tool to do this!

As I can't edit Excel files of xls-format, I created my example in xlsx-format!
And I edited all column headers so they don't contain spaces/special characters. As only data info is added into Tables there, this doesn't have any effect on how the tool is working! The column placement, and it's formatting, is all what counts!
Attached Files
File Type: xlsx TournamentInfo.xlsx (59.6 KB, 6 views)
Reply With Quote
  #4  
Old 11-18-2022, 12:22 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

Thank you ari, maybe I do not understand, or I was not clear.

The tournamentinfo is perfect. What I need is a way to take orders and turn them into tournament info.
Reply With Quote
  #5  
Old 11-18-2022, 01:43 PM
ArviLaanemets ArviLaanemets is offline Convert an excel file with many fields into one with a few specific fields Windows 8 Convert an excel file with many fields into one with a few specific fields Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Sportsmen View Post
The tournamentinfo is perfect. What I need is a way to take orders and turn them into tournament info.
And the tool does exactly this! You paste your orders info into Table in rightmost sheet, and you get the new tournament info (rows which doesn't exist in tournament table jet) in 2nd sheet from left. I added comments in red shrift into every page, where is explained, for what the page/table is for!

Contrary what you did write, I couldn't identiyfy from where to read some info to be put into tournament table from orders table. So this part of info is missing currently, and you have either to add missing formulas yourself (the calculation steps are ordered from right page to left), or you have to give better information, from which columns of orders table this info must be read.
Reply With Quote
  #6  
Old 11-18-2022, 02:06 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
And the tool does exactly this! You paste your orders info into Table in rightmost sheet, and you get the new tournament info (rows which doesn't exist in tournament table jet) in 2nd sheet from left. I added comments in red shrift into every page, where is explained, for what the page/table is for!

Contrary what you did write, I couldn't identiyfy from where to read some info to be put into tournament table from orders table. So this part of info is missing currently, and you have either to add missing formulas yourself (the calculation steps are ordered from right page to left), or you have to give better information, from which columns of orders table this info must be read.
So sorry I missed the different tabs; I am horrible with excel. In the fields, id, exp. State, rate, and local can all be blank. If the state is IL, that is also fine.

I tried it with data from one of our old tournaments. I cleared out everything. I pasted the order information into orders using paste. When I checked out data verification, I see the GR (grade) tab is blank I do need that information. On the team tab, I need it to only list the 5-letter abbreviation, ex WLKCH or MNRHN (not WLKCH: Walker School Clarendon Hills, IL or my program won't read it).

I can't begin to thank you enough. It means so much to me, and you will make hundreds of kids very happy each month.
Attached Files
File Type: xlsx Conversion Test.xlsx (69.2 KB, 3 views)
Reply With Quote
  #7  
Old 11-18-2022, 04:45 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

Apologies, I realized the school names were an issue on our website, I believe I have corrected it, but I am still not sure why the grade is blank. I tried a few more times and could not solve that issue.
Reply With Quote
  #8  
Old 11-18-2022, 11:09 PM
ArviLaanemets ArviLaanemets is offline Convert an excel file with many fields into one with a few specific fields Windows 8 Convert an excel file with many fields into one with a few specific fields Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

I can have a look at Monday - I don't have MS Office installed at home!
Reply With Quote
  #9  
Old 11-19-2022, 04:35 AM
p45cal's Avatar
p45cal p45cal is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You'd better say how you want map these headers:
Order ID
Email
Financial Status
Paid at
Fulfillment Status
Fulfilled at
Currency
Subtotal
Shipping
Taxes
Amount Refunded
Total
Discount Code
Discount Amount
Shipping Method
Created at
Lineitem quantity
Lineitem name
Lineitem price
Lineitem sku
Lineitem variant
Lineitem requires shipping
Lineitem taxable
Lineitem fulfillment status
Billing Name
Billing Address1
Billing Address2
Billing City
Billing Zip
Billing Province
Billing Country
Billing Phone
Shipping Name
Shipping Address1
Shipping Address2
Shipping City
Shipping Zip
Shipping Province
Shipping Country
Shipping Phone
Cancelled at
Private Notes
Channel Type
Channel Name
Channel Order Number
Product Form: Participants First Name
Product Form: Participants Last Name
Product Form: Address
Product Form: Address (Line2)
Product Form: City
Product Form: State
Product Form: Postal Code
Product Form: School
Product Form: Additional Illinois Schools
Product Form: School Grade
Product Form: Email
Product Form: Phone 1
Product Form: Phone 2
Payment Method
Payment Reference

to these:
Name
ID
Exp.
St
Gr
Team
Rate
Local
Reply With Quote
  #10  
Old 11-19-2022, 10:41 AM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

Name participants Last Name, Participants First Name
ID
Exp.
St
Gr School Grade
Team School
Rate
Local

I should have done this before. The only things I need are Participants Lane, First name in the name field, school grade in the GR field, and School in the team field (I adjusted our website, so It should be correct for our next event. All the other fields can be blank since my other program will add them in. However, even though they are blank, I need those fields to be in the sheet, or else my program will not read the file.



Thank you guys for all the help!





Quote:
Originally Posted by p45cal View Post
You'd better say how you want map these headers:
Order ID
Email
Financial Status
Paid at
Fulfillment Status
Fulfilled at
Currency
Subtotal
Shipping
Taxes
Amount Refunded
Total
Discount Code
Discount Amount
Shipping Method
Created at
Lineitem quantity
Lineitem name
Lineitem price
Lineitem sku
Lineitem variant
Lineitem requires shipping
Lineitem taxable
Lineitem fulfillment status
Billing Name
Billing Address1
Billing Address2
Billing City
Billing Zip
Billing Province
Billing Country
Billing Phone
Shipping Name
Shipping Address1
Shipping Address2
Shipping City
Shipping Zip
Shipping Province
Shipping Country
Shipping Phone
Cancelled at
Private Notes
Channel Type
Channel Name
Channel Order Number
Product Form: Participants First Name
Product Form: Participants Last Name
Product Form: Address
Product Form: Address (Line2)
Product Form: City
Product Form: State
Product Form: Postal Code
Product Form: School
Product Form: Additional Illinois Schools
Product Form: School Grade
Product Form: Email
Product Form: Phone 1
Product Form: Phone 2
Payment Method
Payment Reference

to these:
Reply With Quote
  #11  
Old 11-19-2022, 12:04 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

I might have screwed things up even more I went and adjusted our site to make it easier to register, I also adjusted the school codes so it would be the correct format. I deleted a column that screwed it up.

Here is the new download format.

I only need the following

Name participants Last Name, Participants First Name
ID
Exp.
St
Gr School Grade
Team School
Rate
Local

I should have done this before. The only things I need are Participants Lane, First name in the name field, school grade in the GR field, and School in the team field (I adjusted our website, so It should be correct for our next event. All the other fields can be blank since my other program will add them in. However, even though they are blank, I need those fields to be in the sheet, or else my program will not read the file.

Apologies for making this so hard for you guys, and thank you for all the help
Attached Files
File Type: xlsx correct format.xlsx (10.8 KB, 5 views)
Reply With Quote
  #12  
Old 11-19-2022, 01:43 PM
p45cal's Avatar
p45cal p45cal is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Power Query solution in sheet Table1. Right-click somewhere in it and choose Refresh to update from the table in sheet orders (3).
Attached Files
File Type: xlsx msofficeforum50010correct format.xlsx (24.3 KB, 4 views)
Reply With Quote
  #13  
Old 11-19-2022, 02:34 PM
Sportsmen Sportsmen is offline Convert an excel file with many fields into one with a few specific fields Windows 10 Convert an excel file with many fields into one with a few specific fields Office 2019
Novice
Convert an excel file with many fields into one with a few specific fields
 
Join Date: Nov 2022
Posts: 8
Sportsmen is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Power Query solution in sheet Table1. Right-click somewhere in it and choose Refresh to update from the table in sheet orders (3).
It worked!!! Thank you very much!!! Thanks to everyone who helped out, I appreciate it!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save File with specific name from fields input. PM1 Word 17 10-04-2020 07:22 PM
Linking Specific text fields in PP to specific cells in an Excel table GWRW1964 PowerPoint 0 02-26-2018 07:37 AM
Convert an excel file with many fields into one with a few specific fields Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet Labyrinth Word 7 07-19-2016 01:35 PM
Word 2010 - DOCX File with fields -> PDF with fields senglory Word 2 03-27-2012 10:10 AM
Convert an excel file with many fields into one with a few specific fields Read text Report file with VBA and write parsed fields to Excel workbook tpcervelo Excel Programming 1 01-05-2012 10:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft