How to split only one column to multiple rows while copying rest of the columns along the way

0 votes

I have an excel file with numerous rows, and one column contains numerous values that are separated by commas.

Model Vendor Serial Number
S20 ABC 1122334455, 5544332211
S21 XYZ 9988776655, 5566778899, 2244668800

The third column should be divided into rows, which I was able to do with ease. However, I also want to copy columns 1 and 2. Thus, it should seem as follows:

Model Vendor Serial Number
S20 ABC 1122334455
S20 ABC 5544332211
S21 XYZ 9988776655
S21 XYZ 5566778899
S21 XYZ 2244668800

I manually duplicated the remaining columns into the newly constructed one after first delimiting the column. It takes a very long time because there are thousands of records.

Feb 6, 2023 in Others by Kithuzzz
• 38,010 points
333 views

1 answer to this question.

0 votes

Try this:

=DROP(REDUCE(0,REDUCE(0,C2:C3,LAMBDA(a,x,VSTACK(a,CONCAT(CHOOSEROWS(A2:B3,ROW(x)-1)&"|")&TEXTSPLIT(x,,",")))),LAMBDA(p,q,VSTACK(p,TEXTSPLIT(q,"|")))),2)

enter image description here

answered Feb 6, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to sum the value of 2 rows with vlookup by only using 1 formula?

 Try in Excel Online: • Formula used in cell C3 =SUM(SCAN(0,M3:N3,LAMBDA(x,y,VLOOKUP(y,P3:Q12,2,0)))) Works ...READ MORE

answered Jan 17, 2023 in Others by narikkadan
• 63,720 points
369 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,720 points
1,450 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,479 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,679 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
821 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,002 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
1,744 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
621 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP