write data consecutively if certain cell is not empty

[Oryos]

hi everyone,

I'm working on a new macro here to create a table that is used to give me better overview of some important documents.
I have a table with three rows with 1. Country, 2. amount and 3. documents as hearder.

it looks like this:

Country	amount	documents
Country A	100	A
	100	B
country B	...	...

This list goes on for many countries and the values within the row 'amount' are variable meaning that every day I have to put in new data. The rest stays the same.

What I want now is to create a new table with a better overview of documents needed. For example document A might be necessary for 2 or 3 countries and in that new table the document A should only be mentioned once.
Please find attached my file for better understanding of the issue described and my final outcome.

I was trying to put this into an VBA code and this is what i got so far:

Sub run_all()
 
    Dim countryl1 As Long, result1 As String, i As Long
    Range("A45:A52").Clear
   
    country1 = Range("B2") - Range("B3").Value
    country2 = Range("B4") - Range("B5").Value
        For i = 45 To 52
    If country1 >= 1 Then
    result1 = Range("C2")
    result2 = Range("C3")
    If country2 >= 1 Then
    result3 = Range("C5")documents.xlsm
    Else
    result1 = ""
    End If
   
 Range("A45") = result1
Range("A46") = result2
If Range("A46") >= 1 Then
Range("A47") = result3
      End If
        End If
        Next i
          End Sub

my thinking was the following:
IF there is a value of any kind in cell B2 or B3 then write the respective documents found in Cell C2 and C3 in Cell A45. If there is no value in Cell B2 or B3 than leave it blank and move on to check B4 and B5 of values and so on.
The tricky part for me here is this:
1. My code doesnt represent the case when nothing is found for country 1
2. my code doesnt cover the situation that one file has already been found and it doesnt need to be written into my new table.
3. one row to the right of my final outcome there should be an x depending on the document type cause only some documents are necessary to print (see my attached file). How can I implement that?

Is there anybody who could help me here? Any advice is much appreciated!
Thank you

[p45cal]

Please find attached my file for better understanding of the issue described and my final outcome.

No file!!
See if attached guess at your file begins to approach what you want.

[p45cal]

Aha!
I spotted a ref to your file in amongst your code (ATTACH]15296[/ATTACH)and managed to get at it.
In the attached is a button near cell H1. Click it.

[Oryos]

Hi p45cal,

thank you for your quick response and thanks for providing a first solution to my problem!
It looks pretty nice but I have three issues that aren't cover or might not have been so clear my explanation. Sorry for that!

1. The first thing is that a pivot Table is not so well suited for my purposes since its impossible to enter values into it after it was created. My final outcome should look like a table that can be edited by me later.
2. Second, sometimes I only have an amount for one countries but still two documents are needed. Such as for country E. in that case the result should be "document E" and "document H"
3. third, I would like this table to be formated. I think I could do that myself but there is one thing I dont know how to do: all B documents dont need to be printed but uploaded. So I would like to mark the 2 cells to the right of document B in the final outcome with an "x". How can I do that?

Please find attached my edited version of your last file.

Thanks and regards!

[p45cal]

Answers:
1. Easy enough to convert the pivot to a normal range:

  .TableRange2.Copy
  .TableRange2.PasteSpecial Paste:=xlPasteValues

and you can throw in a

Application.CutCopyMode = False

to tidy up.

2. How do we and/or the code know which? How can we tell the code that?
(a)Your example of Country E with document E going wth H. Is it always the case? Does any country requiring document E aso need document H?
(b)…and the reverse, if you had an amount for Country E against document H, would they also need document E?
(c)Does the same (a) apply to country D; is document F always accompanied by document G?

3. One way is to add a column say 'Format' and have F (for file) and P (for printout). Another is to have separate list of documents only requiring downloading, elsewhere on the sheet.

[Oryos]

thanks for your answer.

2. the expression would be the following: IF one amount OR two amounts are given for one country BOTH documents are necessary. In case one of the documents is already in the outcome list, ignore it.
a) its depending on the country which documents is needed. So E doesnt necessarily require H. I could for some countries be document E and G for example. Or any other combination. It depends on the country requirements.
b) the reverse. As long there is one amount for a country, both documents are needed. However, If one of the documents is already required by another country, that specific document doesnt need to be mentioned again in the final outcome list.
c) for each country is a specific set on documents required.

So in my logical expression I would formulate the code like this:
If one or two amounts exist for country A, both documents are needed in case the documents haven't been needed by other Countries as well.
the same for country B: If one or more amounts are existent both documents are needed in case they weren't already covered by country A for example.
If no amount is detected for a country, no documents are needed.

[p45cal]

See if the attached does it. Just remains to add the column for uploading v. printing for which we need more information.

[p45cal]

deleted (duplicate).

[Oryos]

Awesome!! Your macro does a fantastic job! Thank you! Just one question:
With the code below you defined which document is getting an "x" for printing. To be honest, i dont even understand what this formula does but I wonder how I can implement lets say that document H doesnt need to be printed anymore and hence no "x" is needed?

  With .Offset(, 3)
    .FormulaR1C1 = "=IF(SUMIF(R2C1:R11C1,RC[-3],R2C2:R11C2)>0,""x"","""")"
    .Cells(1).Value = "Print"

That code seems to me too complicated to figure out where to set which document gets an x for printing and which doesnt.
Thanks for clarification.

[p45cal]

but I wonder how I can implement lets say that document H doesnt need to be printed anymore and hence no "x" is needed?

As with uploading v printing, you'd need a separate, permanent table somewhere with all the document names and a couple of columns where you can adjust the entries. This would serve as a data source for a more complex formula.
Eg.
Capture3.JPG

[p45cal]

see attached

[Oryos]

thanks a lot p45cal! I'll have a look at it and will get back to you as soon as I can.

[Oryos]

Hi p45cal,

sorry for my late response but I was busy at work. I had the chance to test that macro and it does it's magic! Thank you very much!
I ran into another issue that I want to import a worksheet from another file. I made it work so that the user is prompted to select the file to be imported. But I'm thinking to automate that whole process. I'm gonna close this thread here and open up another one so that it is also easy for others with a similiar issue to find the topic.