Filling Out a Template with Consecutive Fields (1 Viewer)

[RogerCooper]

I need to fill out an Excel template with information on imports, see the attached file Template.txt. The data I need to fill it out from is here under Special Duty.txt or Special Duty Crosstab.txt

To show it visually.

The template
"Import #","Entry Line.","HTS2","HTS3","HTS4","HTS5","HTS6","HTS7","Rate 2","Rate 3","Rate 4","Rate 5","Rate 6","Rate 7"

The data

Entry Summary Number	Entry Summary Line Number	HTS Number - Full	Line Tariff Duty Amount	Line Tariff Goods Value Amount	Rate
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99038803	25
32253291508	99030125	10
32253291508	99030124	20
32253291508	99038803	25
32253291508	99030124	20
32253291508	99030125	10

Special Duty

[td]

1​

[/td]​

[td]

6494.4​

[/td][td]

32472​

[/td]​

[td]

1​

[/td]​

[td]

3247.2​

[/td][td]

32472​

[/td]​

[td]

2​

[/td]​

[td]

6278​

[/td][td]

31390​

[/td]​

[td]

2​

[/td]​

[td]

3139​

[/td][td]

31390​

[/td]​

[td]

3​

[/td]​

[td]

2973.6​

[/td][td]

14868​

[/td]​

[td]

3​

[/td]​

[td]

1486.8​

[/td][td]

14868​

[/td]​

[td]

4​

[/td]​

[td]

51.8​

[/td][td]

259​

[/td]​

[td]

4​

[/td]​

[td]

25.9​

[/td][td]

259​

[/td]​

[td]

4​

[/td]​

[td]

64.75​

[/td][td]

259​

[/td]​

[td]

5​

[/td]​

[td]

423.4​

[/td][td]

4234​

[/td]​

[td]

5​

[/td]​

[td]

846.8​

[/td][td]

4234​

[/td]​

[td]

5​

[/td]​

[td]

1058.5​

[/td][td]

4234​

[/td]​

[td]

6​

[/td]​

[td]

1408​

[/td][td]

7040​

[/td]​

[td]

6​

[/td]​

[td]

704​

[/td][td]

7040​

[/td]​

 

[RogerCooper]

or

Entry Summary Number	Entry Summary Line Number	99030110	99030124	99030125	99030260	99037801	99038508	99038803	99038815
32253291508		20	10
32253291508		20	10
32253291508		20	10
32253291508		20	10				25
32253291508		20	10				25
32253291508		20	10

Special Duty Crosstab

[td]

1​

[/td]​

[td]

2​

[/td]​

[td]

3​

[/td]​

[td]

4​

[/td]​

[td]

5​

[/td]​

[td]

6​

[/td]​

What I need to do is enter the data using the template in consecutive columns, ignoring the blanks. For example line 1 should be

32253291508,1,"99030124","99030125","","","","","20","10","","","",""

Line 4 should be

32253291508,1,"99030124","99030125","99038803","","","","20","10","25","","",""

I know that I can solve this by walking through the data with loops and updating one cell at a time. Is there is a more elegant solution to this problem?

 

[DHookom]

I'm confused since your data file does not contain the value 99030110.
This crosstab is as close as I can get/guess based on your requirements and Special Duty text file.

TRANSFORM Min([Special Duty].Rate) AS MinOfRate
SELECT [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
FROM [Special Duty]
GROUP BY [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
PIVOT [Special Duty].[HTS Number - Full];

 

[RogerCooper]

I'm confused since your data file does not contain the value 99030110.
This crosstab is as close as I can get/guess based on your requirements and Special Duty text file.

TRANSFORM Min([Special Duty].Rate) AS MinOfRate
SELECT [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
FROM [Special Duty]
GROUP BY [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
PIVOT [Special Duty].[HTS Number - Full];

The crosstab had been run against all the data, but I only excerpted a single entry.

 

[DHookom]

Can you please get your data and results to match exactly?

 

[tvanstiphout]

I have a few questions. Please answer all of them. There are good reasons for each of these.
1. Where does Special Duty.txt come from? Do you have any control over its format?
2. Special Duty Crosstab.txt: seems to be the results of a crosstab query over Special Duty data. How was that created? If it was created from Special Duty, then it may not be necessary to create the output file.
3. Special Duty Crosstab.txt: contains the column 99030110 but that is not in the data of Special Duty. It therefore seems there is additional data needed to create this crosstab. Where is that data?
4. Can Special Duty have more than one line with the same [Entry Summary Number] and [Entry Summary Line Number] combination? That is not so in the data you gave us, but can in happen in the more general case?
Thanks,