reformat spreadsheet

Goofydisney

New Member
Joined
Apr 30, 2002
Messages
8
Hi Experts,
I got some problem. I have a spreadsheet in this format:
A1 A2 A3 A4
1 A Pte Ltd SAM Siemens Intel
2 B Pte Ltd
3 C Pte Ltd Mot Hitachi AMD
and the list goes on.

There are some rows with A1 data but A2 onwards are blank. That means, Company with carrying no brand names (eg. B Pte Ltd).

How do I reformat the list to be as below and omit those with no brand names :

A1 A2 A3 A4
1 A Pte Ltd SAM
2 A Pte Ltd Siemens
3 A Pte Ltd Intel
4 C Pte Ltd Mot
5 C Pte Ltd Hitachi
6 C Pte Ltd AMD


Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
why dont you run an auto filter on column 2.
and then filter for "BLANK"
and delete the lines it then shows?
 
Upvote 0
A1= A Pte Ltd ,A2= SAM, A3= Siemens, A4= Intel

to be :
A1: A Pte Ltd
A2: Sam
B1: A Pte Ltd
B2: Siemens
C1: A Pte Ltd
C2: Intel

Sounds tedious.
 
Upvote 0
EVERY1 FEEL FREE TO ASSIST

i am trying to work on a code to
a: count the number of brands in a row
b: copy the brands in the row
c: insert that number of rows(=to number of brands)
d: transpose the brands...
e:goto next line
 
Upvote 0
Number of brands is not fixed for any company. So company A can have 3 , while Company F could have only one. I can't say there is any max number of brands for each company. Range from 0 to x.
 
Upvote 0
Hi goofydisney,
I see your new to this board, hang in there even though a lot of people will jump in w/ the right intentions to help, it's easy to get very,very frustrated. I posted a simple
formula question last nite and was told to "slow down" , "check my calcs" and they even
were joking amongst each other on my thread
even though they couldn't figure out the formula. Then along came the "master" of formulas Aladin, I hope he sees your post.
Good Luck

James
 
Upvote 0
Hi James,
Thanks :) You guys on this board are so helpful. I am from Singapore and yesterday was a holiday (MayDay) and I can't believe it you guys are helping out here.

In Singapore, we say "Give that guy a Tiger Beer!"
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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