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.




342 posts

Ultimate Geek


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

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
1883 posts

Uber Geek

Subscriber

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

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?

 

 




342 posts

Ultimate Geek


  # 2386513 7-Jan-2020 11:11
Send private message quote this post

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


 
 
 
 




342 posts

Ultimate Geek


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

 

 





I Eat Dumbbells for Breakfast


1883 posts

Uber Geek

Subscriber

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

Would use of PIVOT tables help?

 

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

 

 


51 posts

Master Geek


  # 2386522 7-Jan-2020 11:30
2 people support this post
Send private message quote this post


342 posts

Ultimate Geek


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

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


1401 posts

Uber Geek


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

 

 

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

 


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



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Ring launches indoor-only security camera
Posted 23-Jan-2020 17:26


New report findings will help schools implement the digital technologies curriculum content
Posted 23-Jan-2020 17:25


N4L to upgrade & support wireless internet inside schools
Posted 23-Jan-2020 17:22


Netflix releases 21 Studio Ghibli works
Posted 22-Jan-2020 11:42


Vodafone integrates eSIM into device and wearable roadmap
Posted 17-Jan-2020 09:45


Do you need this camera app? Group investigates privacy implications
Posted 16-Jan-2020 03:30


JBL launches headphones range designed for gaming
Posted 13-Jan-2020 09:59


Withings introduces ScanWatch wearable combining ECG and sleep apnea detection
Posted 9-Jan-2020 18:34


NZ Police releases public app
Posted 8-Jan-2020 11:43


Suunto 7 combine sports and smart features on new smartwatch generation
Posted 7-Jan-2020 16:06


Intel brings innovation with technology spanning the cloud, network, edge and PC
Posted 7-Jan-2020 15:54


AMD announces high performance desktop and ultrathin laptop processors
Posted 7-Jan-2020 15:42


AMD unveils four new desktop and mobile GPUs including AMD Radeon RX 5600
Posted 7-Jan-2020 15:32


Consolidation in video streaming market with Spark selling Lightbox to Sky
Posted 19-Dec-2019 09:09


Intel introduces cryogenic control chip to enable quantum computers
Posted 10-Dec-2019 21:32



Geekzone Live »

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


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



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.

Alternatively, you can receive a daily email with Geekzone updates.