Data with 'headings" at the side ...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have received data in an Excel spreadsheet that I wish to use for a mail merge. The data in the spreadsheet, unfortunately, has the columns at the side and the details across the page, which means that the headings are constantly repeated, eg

Name: Jo Blogg
Company: Jo Bloggs & C
City: New Yor
Name: Sue Smit
Company: Sue Smith’s boutiqu
City: Los Angele
Name: John Brow
Company: John Brown Seafood Delight
City: Washingto

Is there an easy way to manipulate this data? Thanks so much.
 
Hi
some questions:
- is the data separated in to columns? (one for the heading and one for
the data)
- are there always exactly 3 entries per dataset?

If yes to both you may try the following:
Assumptions:
- your source data is in sheet 1
- data is in column A+B starting in row 1
- The target data is on a separate sheet, columns A, B, C, row 1 as
heading row:

Try:
- first manually insert the three headings in row 1
- enter the following in A2 (Name column)
=OFFSET('sheet1'!$A$1,INT(ROW()-2)*3,0)
- in B2 (comapny column) enter
=OFFSET('sheet1'!$A$2,INT(ROW()-2)*3,0)
- in C2 enter
=OFFSET('sheet1'!$A$3,INT(ROW()-2)*3,0)

- copy all three formulas down
- After this copy this range (CTRL+C), goto 'Edit - Paste Special' and
choose 'Values' to remove the formulas

--
Regards
Frank Kabel
Frankfurt, Germany

Trish said:
I have received data in an Excel spreadsheet that I wish to use for a
Click to expand...
mail merge. The data in the spreadsheet, unfortunately, has the
columns at the side and the details across the page, which means that
the headings are constantly repeated, eg:
 
Hello Frank, thanks for your response. Regrettably it is a little bit more complicated than I initially suggested - I had tried to simplify it for my posting. Below is an example of just two records. I actually need only the name of the company, and address details. Probably the company secretary and fax number too. Is this exercise bigger than Ben Hur? Is there a workaround by taking it into Access (I did post a similar question in the Access import section, but no response at the time of writing this). I am grateful for your help. Thanks. Tris

ASX Code Detail Value Othe
AAB ASX Code: AAB
AAB Company Name: ABSOLUTE RETURN FUND (THE)
AAB MarketCall Code: 6209
AAB Listing: 20020918
AAB GICS: 00000000-GICS Sector Code Not Applicable
AAB Address Line 1: c/- Absolute Capital
AAB Address Line 2: Level 7
AAB Town: SYDNEY
AAB State: NSW
AAB Postcode: 2000
AAB Telephone: (02) 9230 6400
AAB Fax: (02) 9230 6499
AAB Internet Address: http://www.absolutecapital.com.au/
AAB Share Registry: ASX PERPETUAL REGISTRARS LIMITED
AAB Share Registry Address Line 1: LEVEL 8
AAB Share Registry Address Line 2: 580 GEORGE STREET
AAB Share Registry Town: SYDNEY
AAB Share Registry State: NSW
AAB Share Registry Postcode: 2000
AAB Share Registry Telephone: (02) 8280 7111
AAB Share Registry Fax: (02) 9261 8489
AAB Personnel: Michael Devlin(Chairman) Chairma
AAB Personnel: Deon Joubert(Director) Directo
AAB Personnel: Andrew Wong(Investor Relations) Investor Relation
AAB Personnel: Deon Joubert(Managing Director) Managing Directo
AAB Personnel: Jonathan Eriksen(Non Exec. Director) Non Exec. Directo
AAB Personnel: Monty Muhl(CFO) CF
AAB Personnel: Monty Muhl(Company Secretary) Company Secretar
AAB Principal Activity: The fund is a globally diversified multi-strategy, multi-manager hedge fund and structured debt portfolio.
AAC ASX Code: AAC
AAC Company Name: AUSTRALIAN AGRICULTURAL COMPANY LIMITED.
AAC MarketCall Code: 767
AAC Listing: 2001081
AAC GICS: 30200000-Food Beverage & Tobacc
AAC Address Line 1: Level
AAC Address Line 2: Suite 30
AAC Town: SYDNE
AAC State: NS
AAC Postcode: 200
AAC Telephone: (02) 9248 222
AAC Fax: (02) 9299 896
AAC Internet Address: www.aaco.com.a
AAC Share Registry: ASX PERPETUAL REGISTRARS LIMITE
AAC Share Registry Address Line 1: LEVEL
AAC Share Registry Address Line 2: 580 GEORGE STREE
AAC Share Registry Town: SYDNE
AAC Share Registry State: NS
AAC Share Registry Postcode: 2000
AAC Share Registry Telephone: (02) 8280 7111
AAC Share Registry Fax: (02) 9261 8489
AAC Personnel: Nicholas Burton Taylor(Chairman) Chairma
AAC Personnel: Don Mackay(Executive Director) Executive Directo
AAC Personnel: Ross Aird(Investor Relations) Investor Relation
AAC Personnel: Tim Fischer(Non Exec. Director) Non Exec. Directo
AAC Personnel: Christopher Roberts(Non Exec. Director) Non Exec. Directo
AAC Personnel: David Hills(Non Exec. Director) Non Exec. Directo
AAC Personnel: Charles Bright(Non Exec. Director) Non Exec. Directo
AAC Personnel: Peter Holmes a Court(Non Exec. Director) Non Exec. Directo
AAC Personnel: Don Mackay(CEO) CE
AAC Personnel: Luisa Catanzaro(CFO) CF
AAC Personnel: Luisa Catanzaro(Company Secretary) Company Secretar
AAC Principal Activity: Beef production
 
Hi
still one question: Is this in one cell per entry. That is the heading
+ content in one single cell or in two cells?

--
Regards
Frank Kabel
Frankfurt, Germany

Trish said:
Hello Frank, thanks for your response. Regrettably it is a little
Click to expand...
bit more complicated than I initially suggested - I had tried to
simplify it for my posting. Below is an example of just two records.
I actually need only the name of the company, and address details.
Probably the company secretary and fax number too. Is this exercise
bigger than Ben Hur? Is there a workaround by taking it into Access (I
did post a similar question in the Access import section, but no
response at the time of writing this). I am grateful for your help.
Thanks. Trish
ASX Code Detail Value Other
AAB ASX Code: AAB
AAB Company Name: ABSOLUTE RETURN FUND (THE)
AAB MarketCall Code: 6209
AAB Listing: 20020918
AAB GICS: 00000000-GICS Sector Code Not Applicable
AAB Address Line 1: c/- Absolute Capital
AAB Address Line 2: Level 7
AAB Town: SYDNEY
AAB State: NSW
AAB Postcode: 2000
AAB Telephone: (02) 9230 6400
AAB Fax: (02) 9230 6499
AAB Internet Address: http://www.absolutecapital.com.au/
AAB Share Registry: ASX PERPETUAL REGISTRARS LIMITED
AAB Share Registry Address Line 1: LEVEL 8
AAB Share Registry Address Line 2: 580 GEORGE STREET
AAB Share Registry Town: SYDNEY
AAB Share Registry State: NSW
AAB Share Registry Postcode: 2000
AAB Share Registry Telephone: (02) 8280 7111
AAB Share Registry Fax: (02) 9261 8489
AAB Personnel: Michael Devlin(Chairman) Chairman
AAB Personnel: Deon Joubert(Director) Director
AAB Personnel: Andrew Wong(Investor Relations) Investor Relations
AAB Personnel: Deon Joubert(Managing Director) Managing Director
AAB Personnel: Jonathan Eriksen(Non Exec. Director) Non Exec. Director
AAB Personnel: Monty Muhl(CFO) CFO
AAB Personnel: Monty Muhl(Company Secretary) Company Secretary
AAB Principal Activity: The fund is a globally diversified
Click to expand...
multi-strategy, multi-manager hedge fund and structured debt portfolio.
 
Hi Fran

Firstly, I should tell you that this "Excel spreadsheet" is a "...xls.csv" file. Does that make a difference? To answer your specific question, the data appears to be spread over four columns, A to D, being A (ASX Code), B (Detail) holds the headings, C (Value) holds the data, and D (Other) holds more data, which I don't actually need, ie it holds the positions of the office-bearers, eg Company Secretary, which is already clear from the previous column). Frank, I hope I have answered your question. Your assistance is appreciated, so I hate to waste your valuable time! Cheers. Tris
 
Hi Trish
try the following if you only need some columns:
- filter the data for column B
- delete all data types which are not required
- now adapt the formula I provided to you. Note: You should have a
fixed number of entries per person. Otherwise this won't work.

--
Regards
Frank Kabel
Frankfurt, Germany

Trish said:
Hi Frank

Firstly, I should tell you that this "Excel spreadsheet" is a
Click to expand...
"...xls.csv" file. Does that make a difference? To answer your
specific question, the data appears to be spread over four columns, A
to D, being A (ASX Code), B (Detail) holds the headings, C (Value)
holds the data, and D (Other) holds more data, which I don't actually
need, ie it holds the positions of the office-bearers, eg Company
Secretary, which is already clear from the previous column). Frank, I
hope I have answered your question. Your assistance is appreciated,
so I hate to waste your valuable time! Cheers. Trish
 
Hi

An idea:
Add a column to left your source data, and generate some indicator value for
every row, from where you want export your data. P.e. "1.a " into row with
first name, "1.b" into row with according company name, "1.c" into row with
according city name, "2.a" into row with second name etc. Rows with abundant
unfo are left empty (their value is set to emty string ""). Probably it will
work only, when same details are present for every company/name, and
probably you have tu use some helper columns to count running occurences of
every detail separately (i.e. you havin some (hidden) column (F) listed 1,
2, 3, ... etc when detail "Name" is listed in column C 1st, 2nd, 3rd, ...
etc times, in another column (G) same for Company name, etc. And in column A
p.e. in cell A4 you enter formula like:
=IF(F4>0,F4&".a",IF(G4>0,G4&".b",IF(H4>0,H4&".c","")))
(when you want many columns, then you have to find a better solution to
compose the indicator value, of-course) and copy it into range fitting
source data range.

Now in target table you can use VLOOKUP function to get the according info
from source table data column. I.e. on first datarow the data from row with
"1.a" as indicator into first column, "1.b" as indicator into second column
etc. The lookup argument is composed from using ROW function value.
Something like for A2:
=VLOOKUP(ROW(A1)&".a",Sheet1!$A$1:$D$10000,4,FALSE)
(Here indicator values are in column Sheet1!A:A and data to retrieve in
column Sheet1!D:D)


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Trish said:
Hello Frank, thanks for your response. Regrettably it is a little bit
Click to expand...
more complicated than I initially suggested - I had tried to simplify it for
my posting. Below is an example of just two records. I actually need
only the name of the company, and address details. Probably the company
secretary and fax number too. Is this exercise bigger than Ben Hur? Is
there a workaround by taking it into Access (I did post a similar question
in the Access import section, but no response at the time of writing this).
I am grateful for your help. Thanks. Trish
ASX Code Detail Value Other
AAB ASX Code: AAB
AAB Company Name: ABSOLUTE RETURN FUND (THE)
AAB MarketCall Code: 6209
AAB Listing: 20020918
AAB GICS: 00000000-GICS Sector Code Not Applicable
AAB Address Line 1: c/- Absolute Capital
AAB Address Line 2: Level 7
AAB Town: SYDNEY
AAB State: NSW
AAB Postcode: 2000
AAB Telephone: (02) 9230 6400
AAB Fax: (02) 9230 6499
AAB Internet Address: http://www.absolutecapital.com.au/
AAB Share Registry: ASX PERPETUAL REGISTRARS LIMITED
AAB Share Registry Address Line 1: LEVEL 8
AAB Share Registry Address Line 2: 580 GEORGE STREET
AAB Share Registry Town: SYDNEY
AAB Share Registry State: NSW
AAB Share Registry Postcode: 2000
AAB Share Registry Telephone: (02) 8280 7111
AAB Share Registry Fax: (02) 9261 8489
AAB Personnel: Michael Devlin(Chairman) Chairman
AAB Personnel: Deon Joubert(Director) Director
AAB Personnel: Andrew Wong(Investor Relations) Investor Relations
AAB Personnel: Deon Joubert(Managing Director) Managing Director
AAB Personnel: Jonathan Eriksen(Non Exec. Director) Non Exec. Director
AAB Personnel: Monty Muhl(CFO) CFO
AAB Personnel: Monty Muhl(Company Secretary) Company Secretary
AAB Principal Activity: The fund is a globally diversified multi-strategy,
Click to expand...
multi-manager hedge fund and structured debt portfolio.
 
Back
Top