anonemous
New Member
- Joined
- Dec 2, 2015
- Messages
- 16
- Office Version
- 365
- 2016
- 2010
- Platform
- Windows
- MacOS
Hello folks,
I'm here again with another intricate request, but I believe this is a useful formula in today's data.
I work with multiple databases - one houses some information, and the other houses other information. This is done for security purposes to keep client information confidential from misuse.
So, I am tasked with combining data from more than one source and making a product from this.
In a particular example, I will have two excel sheets going. One with the master list of "data," but general data. The other list will have all the wanted values that apply to the data.
I usually use vlookup between the two sheets, e.g.
VLOOKUP(A2,'OtherSheetName'!$A$1:$H$4000,2,0)
for a typical matching column A's values, pull Column B, C, D, etc and a majority of the time it's completed.
However most recently we started getting data that is going to multiple rows. Let me give a great example below.
We typically use a year and a 'case/order number' affixed to each master list of information, and that is how the data gets joined. In some of the more recent entries, there are multiples of unique row data per single case number:
This is the desired output I am looking for, so I can do pivot tables and perform additional data analysis from the master combined data:
Thank you very much for assisting and reading.
I'm here again with another intricate request, but I believe this is a useful formula in today's data.
I work with multiple databases - one houses some information, and the other houses other information. This is done for security purposes to keep client information confidential from misuse.
So, I am tasked with combining data from more than one source and making a product from this.
In a particular example, I will have two excel sheets going. One with the master list of "data," but general data. The other list will have all the wanted values that apply to the data.
I usually use vlookup between the two sheets, e.g.
VLOOKUP(A2,'OtherSheetName'!$A$1:$H$4000,2,0)
for a typical matching column A's values, pull Column B, C, D, etc and a majority of the time it's completed.
However most recently we started getting data that is going to multiple rows. Let me give a great example below.
We typically use a year and a 'case/order number' affixed to each master list of information, and that is how the data gets joined. In some of the more recent entries, there are multiples of unique row data per single case number:
| Excel 2013/2016 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Order | Person | ||
| 2 | 2018-01 | Joe | ||
| 3 | 2018-02 | John | ||
| 4 | 2018-03 | Julie | ||
| 5 | 2018-04 | Jane | ||
Sheet1 | ||||
| Excel 2013/2016 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Order | Contents | ||
| 2 | 2018-01 | Apples | ||
| 3 | 2018-01 | Carwash liquid | ||
| 4 | 2018-02 | Apple IPHONE cell phone case | ||
| 5 | 2018-02 | Levi men's jeans, 0114581 | ||
| 6 | 2018-03 | Apples | ||
| 7 | 2018-03 | Grapes | ||
| 8 | 2018-03 | Coca-cola | ||
| 9 | 2018-03 | RETURN ORDER: 2017-0801 WOMEN'S SHOES 78871 | ||
| 10 | 2018-03 | Dorito's Chips, Cool Ranch flavor | ||
| 11 | 2018-04 | BOOK ISBN 9781483217000 | ||
Sheet2 | ||||
This is the desired output I am looking for, so I can do pivot tables and perform additional data analysis from the master combined data:
| Excel 2013/2016 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Order | Person | Order Contents | ||
| 2 | 2018-01 | Joe | Apples | ||
| 3 | 2018-01 | Joe | Carwash liquid | ||
| 4 | 2018-02 | John | Apple IPHONE cell phone case | ||
| 5 | 2018-02 | John | Levi men's jeans, 0114581 | ||
| 6 | 2018-03 | Julie | Apples | ||
| 7 | 2018-03 | Julie | Grapes | ||
| 8 | 2018-03 | Julie | Coca-cola | ||
| 9 | 2018-03 | Julie | RETURN ORDER: 2017-0801 WOMEN'S SHOES 78871 | ||
| 10 | 2018-03 | Julie | Dorito's Chips, Cool Ranch flavor | ||
| 11 | 2018-04 | Jane | BOOK ISBN 9781483217000 | ||
Sheet1 | |||||
Thank you very much for assisting and reading.
