Convert an itemised list to a simple list in Excel
Lists come in many shapes and sizes, as well as having many different names.
Two examples are itemised and simple lists. The former is a collection of individual items with associated details, whilst the latter displays them without any extras.
In the video example, an itemised list holds office furniture products alongside the quantity for each in A3:B7:
𝗣𝗿𝗼𝗱𝘂𝗰𝘁, 𝗤𝘂𝗮𝗻𝘁𝗶𝘁𝘆
Office Chair, 10
Desk, 5
Filing Cabinet, 3
Conference Table, 2
Bookshelf, 4
To convert this to a simple list, the formula buildup is:
1️⃣ =𝚁𝙴𝙿𝚃(𝙰𝟹:𝙰𝟽&"-",𝙱𝟹:𝙱𝟽)
In REPT's 𝚝𝚎𝚡𝚝 argument, the product names are referenced (A3:A7) and concatenated with a hyphen (-). In 𝚗𝚞𝚖𝚋𝚎𝚛_𝚝𝚒𝚖𝚎𝚜, the quantities are pointed to (B3:B7).
This returns a spilled range containing each product repeated X number of times according to its value in the Quantity column.
For example, 'Office Chair-' has 10 repetitions:
Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-
2️⃣ =𝙲𝙾𝙽𝙲𝙰𝚃(𝚁𝙴𝙿𝚃(𝙰𝟹:𝙰𝟽&"-",𝙱𝟹:𝙱𝟽))
Nesting the previous formula in CONCAT combines each value to return in the same cell:
Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Office Chair-Desk-Desk-Desk-Desk-Desk-Filing Cabinet-Filing Cabinet-Filing Cabinet-Conference Table-Conference Table-Bookshelf-Bookshelf-Bookshelf-Bookshelf-
3️⃣ =𝚃𝙴𝚇𝚃𝚂𝙿𝙻𝙸𝚃(𝙲𝙾𝙽𝙲𝙰𝚃(𝚁𝙴𝙿𝚃(𝙰𝟹:𝙰𝟽&"-",𝙱𝟹:𝙱𝟽)),,"-",𝚃𝚁𝚄𝙴)
Using TEXTSPLIT, the previous formula is placed in 𝚝𝚎𝚡𝚝, and a hyphen (-) is specified as the [𝚛𝚘𝚠_𝚍𝚎𝚕𝚒𝚖𝚒𝚝𝚎𝚛]. TRUE is given for [𝚒𝚐𝚗𝚘𝚛𝚎_𝚎𝚖𝚙𝚝𝚢] to ensure empty cells are ignored.
The final result is:
𝗣𝗿𝗼𝗱𝘂𝗰𝘁
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Office Chair
Desk
Desk
Desk
Desk
Desk
Filing Cabinet
Filing Cabinet
Filing Cabinet
Conference Table
Conference Table
Bookshelf
Bookshelf
Bookshelf
Bookshelf
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...