Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


KillerHulk

348 posts

Ultimate Geek


#262133 7-Jan-2020 09:54
Send private message

Hi All,

 

I would like to see if there is a simple / easy process to populate data from 2 workbooks, so that i can Upload a CSV file into our ERP system.

 

First Workbook : contains 600 products with (contract Prices)

 

Second Workbook : contains 70+ customer account numbers

 

Now, i Need a simple way to create a 3rd Workbook where i have all the Products from 1st Workbook towards each customers from 2nd workbook.

 

3rd Workbook will have columns : Customer Account No. | Product | Price

 

On Windows 10 - Office 365

 

Thanks in advance...





I Eat Dumbbells for Breakfast


Filter this topic showing only the reply marked as answer Create new topic
Goosey
2346 posts

Uber Geek

Subscriber

  #2386499 7-Jan-2020 10:47
Send private message

Would you simply copy the sheets into a new workbook, then create a new sheet in that 3rd workbook and proceed to use VLOOKUPs to get what you want?

 

 


 
 
 

GoodSync. Easily back up and sync your files with GoodSync. Simple and secure file backup and synchronisation software will ensure that your files are never lost (affiliate link).
KillerHulk

348 posts

Ultimate Geek


  #2386513 7-Jan-2020 11:11
Send private message

Goosey:

 

Would you simply copy the sheets into a new workbook, then create a new sheet in that 3rd workbook and proceed to use VLOOKUPs to get what you want?

 

 

 

 

It Wont, Vlookup wont work.

 

Eventually i want to create a CSV file like this:

 

 

 

 

Account No.

 

Product Name

 

Unit Price

 

 

 

123

 

A

 

10

 

 

 

123

 

B

 

20

 

 

 

123

 

C

 

30

 

 

 

123

 

D

 

40

 

 

 

 

Then, after 600 rows, the Account number changes to 2nd customer and so on for 80 customers.

 

Hopefully i am making some sense here...





I Eat Dumbbells for Breakfast


KillerHulk

348 posts

Ultimate Geek


  #2386514 7-Jan-2020 11:14
Send private message

 

 





I Eat Dumbbells for Breakfast




Goosey
2346 posts

Uber Geek

Subscriber

  #2386516 7-Jan-2020 11:18
Send private message

Would use of PIVOT tables help?

 

This may future proof any changes to each list price category?

 

 


Deamo
159 posts

Master Geek


KillerHulk

348 posts

Ultimate Geek


  #2386549 7-Jan-2020 12:26
Send private message

Deamo:

 

Cross join in Excel:

 

https://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/

 

 

 

 

 

 

Life saver mate...

 

That worked like a charm !!! 👍





I Eat Dumbbells for Breakfast


TwoSeven
1512 posts

Uber Geek

Subscriber

  #2386910 7-Jan-2020 18:32
Send private message

 

 

Basically in Excel 2016 and later and the Office 365 versions one has Power Query (it is the data tab).

 

it allows one to bring in data from different sources (including the current workbook And other workbooks), slice and dice it and output to a new destination.

 

 In the power query editor one can use the M language to do more precise operations. I believe one can also output to CSV

 

 

 

 





Software Engineer
   (the practice of real science, engineering and management)


Filter this topic showing only the reply marked as answer Create new topic





News and reviews »

New Air Traffic Management Platform and Resilient Buildings a Milestone for Airways
Posted 6-Dec-2023 05:00


Logitech G Launches New Flagship Console Wireless Gaming Headset Astro A50 X
Posted 5-Dec-2023 21:00


NordVPN Helps Users Protect Themselves From Vulnerable Apps
Posted 5-Dec-2023 14:27


First-of-its-Kind Flight Trials Integrate Uncrewed Aircraft Into Controlled Airspace
Posted 5-Dec-2023 13:59


Prodigi Technology Services Announces Strategic Acquisition of Conex
Posted 4-Dec-2023 09:33


Samsung Announces Galaxy AI
Posted 28-Nov-2023 14:48


Epson Launches EH-LS650 Ultra Short Throw Smart Streaming Laser Projector
Posted 28-Nov-2023 14:38


Fitbit Charge 6 Review 
Posted 27-Nov-2023 16:21


Cisco Launches New Research Highlighting Gap in Preparedness for AI
Posted 23-Nov-2023 15:50


Seagate Takes Block Storage System to New Heights Reaching 2.5 PB
Posted 23-Nov-2023 15:45


Seagate Nytro 4350 NVMe SSD Delivers Consistent Application Performance and High QoS to Data Centers
Posted 23-Nov-2023 15:38


Amazon Fire TV Stick 4k Max (2nd Generation) Review
Posted 14-Nov-2023 16:17


Over half of New Zealand adults surveyed concerned about AI shopping scams
Posted 3-Nov-2023 10:42


Super Mario Bros. Wonder Launches on Nintendo Switch
Posted 24-Oct-2023 10:56


Google Releases Nest WiFi Pro in New Zealand
Posted 24-Oct-2023 10:18









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.







Pluralsight