Concatenate data from two lists with inequal rows

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have the following sets of data where I need to apply a concatenate function to create a single unified list;

Book1
DEF
3List - 1List - 2
403700002711NR73A-K1H
50370000272A1A30A-KGJ
603700002735RA32F-KGJ
70370000273A1A77A-KGJ
80370000276N7L37A
92Z600F-KGJ
103JA05AN-150
11A1A56A-KGJ
122Z613F-KGJ
133GZ25A-KGJ
14K5L35A
15Y3D03A
16Z7C07A
173PZ35A-KGJ
18D7P27A
Sheet1
Cell Formulas
RangeFormula
F4:F19F4=UNIQUE(O:O,FALSE,FALSE)
Dynamic array formulas.


List - 1 is static while List - 2 is dynamic and is subject to change.
The requirement is to have a concatenated list with each row from List - 1 concatenated with each row from List - 2 to create a single list.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Since you have not provided column O, when I copy and paste to my spreadsheet, all values in column F are zero. Please amend your XL2BB to include column O.
 
Upvote 0
alansidman said:
Since you have not provided column O, when I copy and paste to my spreadsheet, all values in column F are zero. Please amend your XL2BB to include column O.
Click to expand...
List - 2 was populated using the UNIQUE function. Please use copy and paste values to retain the values of column F.
 
Upvote 0
Already tried that. Doesn't work with what you supplied. If you are not going to offer a full workbook of essential data, then I am out of here. Good Luck
 
Upvote 0
Can you post some sample results.
 
Upvote 0
alansidman said:
Already tried that. Doesn't work with what you supplied. If you are not going to offer a full workbook of essential data, then I am out of here. Good Luck
Click to expand...

Here you go.

Book9
ABCDEF
1List - 1List - 21NR73A-K1H
203700002711NR73A-K1HA1A30A-KGJ
30370000272A1A30A-KGJ1NR73A-K1H
403700002735RA32F-KGJA1A30A-KGJ
50370000273A1A77A-KGJ5RA32F-KGJ
60370000276N7L37AA1A30A-KGJ
72Z600F-KGJA1A77A-KGJ
83JA05AN-150N7L37A
9A1A56A-KGJ2Z600F-KGJ
102Z613F-KGJ3JA05AN-150
113GZ25A-KGJA1A56A-KGJ
12K5L35A5RA32F-KGJ
13Y3D03A2Z613F-KGJ
14Z7C07A5RA32F-KGJ
153PZ35A-KGJ2Z600F-KGJ
16D7P27A2Z600F-KGJ
170A1A77A-KGJ
183GZ25A-KGJ
19K5L35A
203GZ25A-KGJ
21K5L35A
22Y3D03A
23Z7C07A
243PZ35A-KGJ
25D7P27A
263PZ35A-KGJ
27D7P27A
283GZ25A-KGJ
29K5L35A
30Y3D03A
31Z7C07A
323GZ25A-KGJ
Sheet3
Cell Formulas
RangeFormula
D2:D17D2=UNIQUE(F:F,FALSE,FALSE)
Dynamic array formulas.
 
Upvote 0
Maybe this?
Book1
ABCDEFGH
1List - 1List - 2Result
23700002711NR73A-K1H370000271-1NR73A-K1H
3370000272A1A30A-KGJ370000271-A1A30A-KGJ
43700002735RA32F-KGJ370000271-5RA32F-KGJ
5370000273A1A77A-KGJ370000271-A1A77A-KGJ
6370000276N7L37A370000271-N7L37A
72Z600F-KGJ370000271-2Z600F-KGJ
83JA05AN-150370000271-3JA05AN-150
9A1A56A-KGJ370000271-A1A56A-KGJ
102Z613F-KGJ370000271-2Z613F-KGJ
113GZ25A-KGJ370000271-3GZ25A-KGJ
12K5L35A370000271-K5L35A
13Y3D03A370000271-Y3D03A
14Z7C07A370000271-Z7C07A
153PZ35A-KGJ370000271-3PZ35A-KGJ
16D7P27A370000271-D7P27A
17370000272-1NR73A-K1H
Sheet2
Cell Formulas
RangeFormula
F2:F76F2=TOCOL(B2:B6& "-"&TOROW(UNIQUE(D2:D16)))
Dynamic array formulas.
 
Upvote 0
Cubist said:
Maybe this?
Book1
ABCDEFGH
1List - 1List - 2Result
23700002711NR73A-K1H370000271-1NR73A-K1H
3370000272A1A30A-KGJ370000271-A1A30A-KGJ
43700002735RA32F-KGJ370000271-5RA32F-KGJ
5370000273A1A77A-KGJ370000271-A1A77A-KGJ
6370000276N7L37A370000271-N7L37A
72Z600F-KGJ370000271-2Z600F-KGJ
83JA05AN-150370000271-3JA05AN-150
9A1A56A-KGJ370000271-A1A56A-KGJ
102Z613F-KGJ370000271-2Z613F-KGJ
113GZ25A-KGJ370000271-3GZ25A-KGJ
12K5L35A370000271-K5L35A
13Y3D03A370000271-Y3D03A
14Z7C07A370000271-Z7C07A
153PZ35A-KGJ370000271-3PZ35A-KGJ
16D7P27A370000271-D7P27A
17370000272-1NR73A-K1H
Sheet2
Cell Formulas
RangeFormula
F2:F76F2=TOCOL(B2:B6& "-"&TOROW(UNIQUE(D2:D16)))
Dynamic array formulas.
Click to expand...
thank you it worked. However, I'm faced with another challenge as I had not considered the PLC status of the SKUs for demand rollover.
To be able to roll over demand from the predecessor to the NPI, I need to create a relationship with the Account IDs so that when I apply the lookup function, I'll see the predecessor demand against the NPI.

Here is an example of how the data capture would look like.

Sample For Formulas & Functions.xlsx
NOPQRSTUVWXYZAA
2Account_ProductPredecessorApr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24
30370000271|A1A77A-KGJ0370000271|1NR73A-K1H122000222041402
40370000272|A1A77A-KGJ0370000272|1NR73A-K1H122000222041402
50370000273|A1A77A-KGJ0370000273|1NR73A-K1H122000222041402
60370000273|A1A77A-KGJ0370000273|1NR73A-K1H122000222041402
70370000276|A1A77A-KGJ0370000276|1NR73A-K1H122000222041402
Sheet4


Sample For Formulas & Functions.xlsx
ABCDEFGHIJK
1Account IDsMaterialPredecessorPLC
203700002711NR73A-K1HEoL0370000271|5RA32F-KGJ0370000271|
30370000272A1A30A-KGJEoL0370000271|A1A77A-KGJ0370000271|
403700002735RA32F-KGJ0370000271|N7L37A0370000271|
50370000273A1A77A-KGJ1NR73A-K1H0370000271|2Z600F-KGJ0370000271|1NR73A-K1H
60370000276N7L37A0370000271|3JA05AN-1500370000271|
72Z600F-KGJ0370000271|A1A56A-KGJ0370000271|
83JA05AN-1500370000271|2Z613F-KGJ0370000271|
9A1A56A-KGJA1A30A-KGJ0370000271|K5L35A0370000271|A1A30A-KGJ
102Z613F-KGJ0370000271|Y3D03A0370000271|
113GZ25A-KGJEoL0370000271|Z7C07A0370000271|
12K5L35A0370000271|3PZ35A-KGJ0370000271|
13Y3D03A0370000271|D7P27A0370000271|
14Z7C07A0370000272|5RA32F-KGJ0370000271|
153PZ35A-KGJ3GZ25A-KGJ0370000272|A1A77A-KGJ0370000271|3GZ25A-KGJ
16D7P27A0370000272|N7L37A0370000271|
170370000272|2Z600F-KGJ0370000272|
180370000272|3JA05AN-1500370000272|
190370000272|A1A56A-KGJ0370000272|
200370000272|2Z613F-KGJ0370000272|1NR73A-K1H
210370000272|K5L35A0370000272|
220370000272|Y3D03A0370000272|
230370000272|Z7C07A0370000272|
240370000272|3PZ35A-KGJ0370000272|A1A30A-KGJ
250370000272|D7P27A0370000272|
260370000273|5RA32F-KGJ0370000272|
270370000273|A1A77A-KGJ0370000272|
280370000273|N7L37A0370000272|
290370000273|2Z600F-KGJ0370000272|
300370000273|3JA05AN-1500370000272|3GZ25A-KGJ
310370000273|A1A56A-KGJ0370000272|
320370000273|2Z613F-KGJ0370000273|
330370000273|K5L35A0370000273|
340370000273|Y3D03A0370000273|
350370000273|Z7C07A0370000273|1NR73A-K1H
360370000273|3PZ35A-KGJ0370000273|
370370000273|D7P27A0370000273|
380370000273|5RA32F-KGJ0370000273|
390370000273|A1A77A-KGJ0370000273|A1A30A-KGJ
400370000273|N7L37A0370000273|
410370000273|2Z600F-KGJ0370000273|
420370000273|3JA05AN-1500370000273|
430370000273|A1A56A-KGJ0370000273|
440370000273|2Z613F-KGJ0370000273|
450370000273|K5L35A0370000273|3GZ25A-KGJ
460370000273|Y3D03A0370000273|
470370000273|Z7C07A0370000273|
480370000273|3PZ35A-KGJ0370000273|
490370000273|D7P27A0370000273|
500370000276|5RA32F-KGJ0370000273|1NR73A-K1H
510370000276|A1A77A-KGJ0370000273|
520370000276|N7L37A0370000273|
530370000276|2Z600F-KGJ0370000273|
540370000276|3JA05AN-1500370000273|A1A30A-KGJ
550370000276|A1A56A-KGJ0370000273|
560370000276|2Z613F-KGJ0370000273|
570370000276|K5L35A0370000273|
580370000276|Y3D03A0370000273|
590370000276|Z7C07A0370000273|
600370000276|3PZ35A-KGJ0370000273|3GZ25A-KGJ
610370000276|D7P27A0370000273|
620370000276|
630370000276|
640370000276|
650370000276|1NR73A-K1H
660370000276|
670370000276|
680370000276|
690370000276|A1A30A-KGJ
700370000276|
710370000276|
720370000276|
730370000276|
740370000276|
750370000276|3GZ25A-KGJ
760370000276|
77
Sheet4
Cell Formulas
RangeFormula
H2:H61H2=TOCOL(TableA[Account IDs]&"|"&TOROW(FILTER(TableB[Material],TableB[PLC]="","")))
J2:J76J2=TOCOL(TableA[Account IDs]&"|"&TOROW(TableB[Predecessor]))
Dynamic array formulas.
 
Upvote 0
For columns with blanks, you can add ignore blanks parameter in the TOROW. I don't see how you're incorporating the PLC column.
Book2
ABCDEFGHIJ
1Account IDsMaterialPredecessorPLC
23700002711NR73A-K1HEoL370000271|1NR73A-K1H
30370000272A1A30A-KGJEoL370000271|A1A30A-KGJ
43700002735RA32F-KGJ370000271|3GZ25A-KGJ
5370000273A1A77A-KGJ1NR73A-K1H0370000272|1NR73A-K1H
6370000276N7L37A0370000272|A1A30A-KGJ
72Z600F-KGJ0370000272|3GZ25A-KGJ
83JA05AN-150370000273|1NR73A-K1H
9A1A56A-KGJA1A30A-KGJ370000273|A1A30A-KGJ
102Z613F-KGJ370000273|3GZ25A-KGJ
113GZ25A-KGJEoL370000273|1NR73A-K1H
12K5L35A370000273|A1A30A-KGJ
13Y3D03A370000273|3GZ25A-KGJ
14Z7C07A370000276|1NR73A-K1H
153PZ35A-KGJ3GZ25A-KGJ370000276|A1A30A-KGJ
16D7P27A370000276|3GZ25A-KGJ
Sheet1
Cell Formulas
RangeFormula
I2:I16I2=TOCOL(B2:B6 & "|" & TOROW(E2:E16,1))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
How to merge the data in a number of cells in a list based on a reference
Replies
9
Views
91
Peter_SSs
Peter_SSs
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
281
hagia_sofia
hagia_sofia
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
108
Lux Aeterna
L
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
306
cydmm
C
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
311
etaf
etaf

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top