|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Quote:
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. |
#6
|
|||
|
|||
Quote:
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. |
#7
|
|||
|
|||
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.
|
#8
|
|||
|
|||
I can have a look at Monday - I don't have MS Office installed at home!
|
#9
|
||||
|
||||
You'd better say how you want map these headers:
Order ID 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 |
#10
|
|||
|
|||
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:
|
#11
|
|||
|
|||
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 |
#12
|
||||
|
||||
Power Query solution in sheet Table1. Right-click somewhere in it and choose Refresh to update from the table in sheet orders (3).
|
#13
|
|||
|
|||
It worked!!! Thank you very much!!! Thanks to everyone who helped out, I appreciate it!
|
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 |
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 |
Read text Report file with VBA and write parsed fields to Excel workbook | tpcervelo | Excel Programming | 1 | 01-05-2012 10:14 PM |